Hi all,

I have a model that has a value amt. These are categorised and each category has its own kpi target %. The % add up to 100%

I'm tracking the actual % using:=sum(amt grouped by category)/calculate(sum(amt),all(tbl))

The first category target is the MINIMUM it needs to hit. The remainder are the MAXIMUM

Here's how it's working now. The 1st row should be green because 74.4>70.0

but i'm not sure how i can get this working.

here's what i mean. Unpaid status is what i have now, the should be is what i want it to show.

my fact is (categoryid,amount), my dimcategory is (categoryid,targetpercentage) - target % as shown above

my measures are

AllUnpaid:=sum(fact[amount])/calculate(sum(fact[amount]),ALL(fact))

- this gives me the actual %

targetPercenage:=sum(targetpercentage)

- this gives me the target %

I then set up the KPI on AllUnpaid and use targetpercentage as the calc column.

I have the following hack working, but it seems like a very roundabout way of doing things and i need to have two measures. one for actual % and a dummy 1/0 for the KPI

step 1. get the subtotal per category as a column on my fact table

=CALCULATE(SUM([amt]), 
           FILTER('fact, 
                  'fact'[CategoryID]=EARLIER('fact'[CategoryID])
                 )
          )

step 2. get the subtotal as % of grand total as a column on the fact

=[CalculatedColumn1]/sum([amt])

step 3. drag across the target % as a column on the fact

=related('Aged Debtor Band'[TargetPercentage])

step 4. case statement. Sets KPI to true if % >= target for categoryid 1. The remainder must be below the target to get 1

=switch(true,
AND([CategoryID]=1,[CalculatedColumn2]>=[CalculatedColumn3]),1,
AND([CategoryID]<>1,[[CalculatedColumn2]<=[CalculatedColumn3]),1,0)

add kpimet:=max(calculatedcolumn4) measure and set absolute kpi on it.

It works but now i have all this nonsense in my model and I have my actual %, target % and kpi status as separate measures whereas logically they should all be different properties of the same measure.

Thanks

just wondering if there's a better way?

Hi Jakub,

this is not so easy to understand. But one thing that wonders me is why you don't take the necessary Category split into account in this formula:

AllUnpaid:=sum(fact[amount])/calculate(sum(fact[amount]),ALL(fact))

Try: AllUnpaid:=sum(fact[amount])/calculate(sum(fact[amount]),ALLEXCEPT(fact, CategoryID))

Hi Imke,

Basically, the "direction" of my KPI targets change.

I want at least 70% of my amt to be in category 1 (>=.7)
I want at most 21% of my amt to be in category 2 (<=.21)
I want at most 6% of my amt to be in category 3 (<=.06)
I want at most 2% of my amt to be in category 4 (<=.02)
I want at most 1% of my amt to be in category 5 (<=.01)
I don't want any of my amt to be in category 6 (<=0)

This is to do with aged debt reporting. It's to track how good my debt collecting is.

Just wondering if there's a way to do this with a single measure and kpi definition, or whether I need to split it up across multiple steps and have a generic "1 0" measure with a KPI against it where only the status has meaning. I'd need to have the actual % and target % as their own explicit measures