Forum Discussion

Rudyi's avatar
Rudyi
Icon for Helper I rankHelper I
6 years ago

PowerKPI with categories - KPI indicator issue

Hi all,

I'm trying to use PowerKPI to show the historical changes in sales amounts per day compared to the threshold set.

The problem is that my dataset has categories, so that it's "category-date-amount-threshold".

According to the tutorial, which came with visual, I introduced a column for KPI index calculation

KPI Index = if(VAR>0,1,IF(VAR<0,2,if(VAR=0,3,4))),
where VAR = IFERROR(if(Threshold<> 0,Sales/Threshold-1,0),BLANK())
If I have category selected - KPI index works perfectly, but I want to check for all categories together - doesn't work, since KPI Indicator Index requires aggregation, so I've put sum. 
Is there any way to get my scenario working to have it both on category and total level?
Also, which formula is exactly used for Variance in the visual? I have cases, where threshold is not set and I want to set some KPI indicator value, which would be same to current, but catching the exceptions for Infinity, since it also messes with KPI indicators. 
 
Thanks!
Need Help

2 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Rudyi ,

    Can you please share a dummy pbix? You could upload it to OneDrive for Business and share the link to us. We will understand clearly about your situation. If you can't do it, please share some sample data and the expected result.  And it is also helpful to share a few screenshots. 

    • Rudyi's avatar
      Rudyi
      Icon for Helper I rankHelper I
      6 years ago

      hi v-xuding-msft ,

       sure thing HERE's a dummy file.

       
       

      If I have category selected (e.g. A) - everything is ok, variance is calculated automatically in the visual (-10%) and index icon and

      color are set according to settings

       

      Same with Category B

      But if all categories are together - KPI is not formated, since SUM is getting used (eq 20)

      None of the aggregation functions seems to work here... that's why I have a feeling it should be done somehow differently 😞
      And the second issue is if threshold is 0 in this case default KPI calculation goes to +Infitity

      That's why I want to get same calculation as the visual does, but with error handling for Infinity and use it in the 


      Hope I made it clear

      Thanks for any advise!