Forum Discussion

Fabio74's avatar
Fabio74
Helper I
5 years ago
Solved

Issue with a measure using Power KPI Matrix

Hi everyone, I'm having troubles understanding how to create a dynamic measure that works in a Power KPI Matrix visualization.

My table contains Persons (4), Fruits (3), quantity per day (0 or 1) and dates (daily from Jan to May)

 

I'm usign a Power KPI Matrix visualization because I'd like to show (for each person) the trend of each fruit consumption per month, which is done with the Sparkline, but I'd also like to show the variance between last month values and the average value (columns highlighted in yellow): 

 

The results shown above for the yellow columns are my desired outcome, but the only way I've managed to obtain it is by using the number "5" (5 months from Jan to May) in my denominator's measure "Mo Avg Qty". I have also created the measure "Distinct Month" (commented in the formula below) to calculate the number of months, but it doesn't work.

Mo Avg Qty = CALCULATE(SUM('Data Table'[Qty]), ALLEXCEPT('Data Table', 'Data Table'[Person],'Data Table'[Fruit])) / 5 --([Distinct Month])

I'm sure I'm missing something (as always with DAX) but I can't find a way to make it work.

Can someone please help me?

Here's a copy of the Pbix file: https://www.dropbox.com/s/w3psii7s46mmm43/MockBi2.pbix?dl=0

 

Secondarily ... does anybody know if there are other visualizations allowing the representation for a category and a subcategory?  The other ones I've found (Sparkline by OKViz or Smart KPI List) only work with one category. I'm asking because Power KPI Matrix shows the result for subcategories but doesn't seem to work for category (unless I'm wrong): 

 

Thank you very much, 

Fabio 

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Fabio74 ,

     

    The measure you have written gives you the incorrect value you need to redo it to:

    Mo Avg Qty = 
DIVIDE (
    CALCULATE (
        SUM ( 'Data Table'[Qty] ),
         ALLSELECTED(Dates)
    ),
    CALCULATE ( DISTINCTCOUNT ( Dates[EndOfMonth] ), ALLSELECTED ( Dates ) )
)

     

    Check result attach.

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Fabio74 ,

     

    Try to change your measure to:

     

    Mo Avg Qty = 
DIVIDE (
    CALCULATE (
        SUM ( 'Data Table'[Qty] ),
        ALLEXCEPT ( 'Data Table', 'Data Table'[Person], 'Data Table'[Item] )
    ),
    CALCULATE ( DISTINCTCOUNT ( Dates[EndOfMonth] ), ALLSELECTED ( Dates ) )
)

     

    The last part of the calculate picks up all the selected values that and count the distinct of the values result varies with slicer selection.

     

    Regarding the second question what do you want to show precisely? Do not understand what is the expected end result you are trying to achieve.

     

    • Fabio74's avatar
      Fabio74
      Helper I
      5 years ago

      Dear MFelix , thank you for your reply. I've modified the measure as you've explained and it works, but when I use the Months filter/slicer ... it doesn't seem to work anymore:

       

       As for the other question, I'd like to replicate this visualization made in an Excel file. As you can see, the Trend is shown for each category and subcategory 

       

      whereas in my PowerBi visualization, the trend is shown only for the subcategory. When I click on the "arrow" to collapse the category, no trend is shown (but maybe the visualization doesn't support this feature).

       

      Hope is clearer now. Thanks!