Issue with a measure using Power KPI Matrix

[Fabio74]

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 

[MFelix]

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]

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!

[MFelix]

Hi @Fabio74 ,

I just did the change of the month calculations instead of having hard coded 5 it counts the number of months so when you select 1 the values is the same has the total.

Do you want to calculate the average for the selected months and fruits for that person?

Regarding the second part try the following custom visual:

https://appsource.microsoft.com/en/product/power-bi-visuals/wa200002816?tab=overview

[Fabio74]

Do you want to calculate the average for the selected months and fruits for that person?

---

Yes please, sorry if it wasn't clear 🙂

As for the visual, I've also taken a quick look at the Multiple Sparklines one, but it seemed to me that it worked only at the category level. But maybe I was wrong. I'll take a second look. Thanks!

[MFelix · Accepted solution]

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.

[Fabio74]

Thank you very much @MFelix , it works. As I have a moment I'll try and read again what you wrote to understand the "how" (which is the most difficult part for me).

Thanks again!