Weighted average per category in a filtered chart (or not): calculated measure

[Anonymous]

Hello,

I am building a dashbord to represent KPIs through time with Area charts like this:

My dataset is a list of KPIs at different dates for several locations, with the information related to the size on which the KPI was based, here [Number of cars]

Location	Date	Number of cars	KPI 1	KPI 2
Germany	11/01/2020	50	100%	95%
US	12/01/2020	200	98%	78%
Germany	12/01/2020	70	58%	14%
Germany	12/01/2020	80	86%	45%

I have several locations that will be filtered or not on the dashboard:

if a location is selected in another chart, my KPI chart (showned above) will display the percentages values [KPI 1] for the location through time [Date], so far so good I made it using the simple table columns I have so far.

Now if nothing is selected, I want to display the KPIs for all countries but weighted with the total number of cars accross all location at that time, and here I am blocking.

Any idea if I should create a new measure or transform my data ?

[MFelix · Accepted solution]

Hi @Anonymous ,

First of all the number you calculate are rounded in my calculation they are not rounded on the calculation but on the format so there is a small diffence to what you have, but calculating without rounding I have the same values.

Create the following two measures:

Number_Cars KPI1 = 
VAR Number_Cars =
    SUMMARIZE (
        'Table',
        'Table'[Location],
        'Table'[Date],
        'Table'[Number of cars],
        'Table'[KPI 1],
        "Total_Cars",
            VAR Total_Cars_By_Day =
                FILTER (
                    SUMMARIZE (
                        ALL ( 'Table' ),
                        'Table'[Location],
                        'Table'[Date],
                        'Table'[Number of cars],
                        'Table'[KPI 1]
                    ),
                    'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] )
                )
            RETURN
                SUMX ( Total_Cars_By_Day, 'Table'[Number of cars] )
    )
RETURN
    SUMX ( Number_Cars, 'Table'[Number of cars] / [Total_Cars] * 'Table'[KPI 1] )


KPI = IF(ISFILTERED('Table'[Location]), AVERAGE('Table'[KPI 1]), [Number_Cars KPI1])

Now the result is has you need:

Just two things to be aware on the Number_of_cars calculation you need to replace the KPI 1 for each KPI you need to calculate, second thing is that I used the average value for the KPI but this can be a sum or whatever value you need.

PBIX file attach.

[MFelix]

Hi @Anonymous ,

When you refer that you want to have the calcution made by total of cars what do you mean do you want to have for germany the sum of 50 + 70 + 80 divided by the total number of cars 50 + 70 + 80 + 200 and then multiply this values by the KPI's?

You need to use a measure similar to the one below:

Measure = 
IF (
    ISFILTERED ( 'Table'[Location] );
    DIVIDE (
        SUM ( 'Table'[Number of cars] );
        CALCULATE ( SUM ( 'Table'[Number of cars] ); ALL ( 'Table'[Location] ) )
    )
        * AVERAGE ( 'Table'[KPI 1] );
    AVERAGE ( 'Table'[KPI 1] )
)

Be aware that the part that you need is the ISFILTERED the rest is just the calculation simple, if you can share on do you calculate the weigth average and used it with the KPI I can adjust it for you.

[Anonymous]

Hello @MFelix ,

Thank you for the code! I actually used it half way to display value when filtered,  but it's on the If Not that I am blocking.

---

When you refer that you want to have the calcution made by total of cars what do you mean do you want to have for germany the sum of 50 + 70 + 80 divided by the total number of cars 50 + 70 + 80 + 200 and then multiply this values by the KPI's?

---

Almost 🙂
What I want when nothing is selected is a KPI weighting all the locations KPI by respective number of cars and date, an example for KPI 1:

a- row value in 'Table'[Number of cars]
b- sum of all the values in the column 'Table'[Number of cars] with the same date
c- row value in 'Table'[KPI 1]

Sum(a/b*c)

So with my data 'Table' and for KPI 1:

on 11/01/2020:

50 / 50 * 100% = 100%

on 12/01/2020:

sum(
200 / (200+70+80) * 98% = 0.56
70 / (200+70+80) * 58% = 0.11
80 / (200+70+80) * 86% = 0.19
) = 86%

So when no location is selected, my weighted-average area chart will display 100% on 11/01/2020 and 86% on 12/01/2020

[MFelix · Accepted solution]

Hi @Anonymous ,

First of all the number you calculate are rounded in my calculation they are not rounded on the calculation but on the format so there is a small diffence to what you have, but calculating without rounding I have the same values.

Create the following two measures:

Number_Cars KPI1 = 
VAR Number_Cars =
    SUMMARIZE (
        'Table',
        'Table'[Location],
        'Table'[Date],
        'Table'[Number of cars],
        'Table'[KPI 1],
        "Total_Cars",
            VAR Total_Cars_By_Day =
                FILTER (
                    SUMMARIZE (
                        ALL ( 'Table' ),
                        'Table'[Location],
                        'Table'[Date],
                        'Table'[Number of cars],
                        'Table'[KPI 1]
                    ),
                    'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] )
                )
            RETURN
                SUMX ( Total_Cars_By_Day, 'Table'[Number of cars] )
    )
RETURN
    SUMX ( Number_Cars, 'Table'[Number of cars] / [Total_Cars] * 'Table'[KPI 1] )


KPI = IF(ISFILTERED('Table'[Location]), AVERAGE('Table'[KPI 1]), [Number_Cars KPI1])

Now the result is has you need:

Just two things to be aware on the Number_of_cars calculation you need to replace the KPI 1 for each KPI you need to calculate, second thing is that I used the average value for the KPI but this can be a sum or whatever value you need.

PBIX file attach.

[Anonymous]

@MFelix,

Thank you for your time, energy and detailed proposition.

In reality I have only 1 KPI value per location and date so it's okay if we use average as you specified 🙂 , result will be same.

I am trying to implement this with my own data but I am not sure to understand, I do understand the measure "KPI"

KPI = IF(ISFILTERED('Table'[Location]), AVERAGE('Table'[KPI 1]), [Number_Cars KPI1])

But I thought you would be using this measure in the Values field for the graph.

The graph is reacting like if we were using this measure : if a location is selected we just take the KPI 1 for the location, and if no location filter then it is refering to the [Number_Cars KPI1] measure, yet on your graph you just use [Number_Cars KPI1] as a value, and it still works. Am I missing something ?

[MFelix]

Hi @Anonymous,

The measure KPI must be used on the line chart, regarding the way they interact with other visualizations this depends on the setup of this visualizations.

The one you are refering is setup in what way? 

[Anonymous]

The solution works thanks a lot !

However I realize something is biaising the result: it can happen that we are missing value for a KPI ( empty value ) like this :

In that situation I am under the impression that the code will compute the weighted KPI 1 as such:

50 / (50+300) * 100% = 14

300 / (50+300) * 0% = 0

14+0 = 14%, while I don't want to include the Number of cars for the empty KPI and should just have 100% as a result.

@MFelix Could we include a non-empty cell requirement in the measure [Number_Cars KPI1] ?

(I accepted your previous solution because I didn't specified this initially)

[MFelix · Accepted solution]

Hi @Anonymous 

Redo the measure number of car to the following:

Number_Cars KPI1 =
VAR Number_Cars =
    SUMMARIZE (
        'Table',
        'Table'[Location],
        'Table'[Date],
        'Table'[Number of cars],
        'Table'[KPI 1],
        "Total_Cars",
            VAR Total_Cars_By_Day =
                FILTER (
                    SUMMARIZE (
                        ALL ( 'Table' ),
                        'Table'[Location],
                        'Table'[Date],
                        'Table'[Number of cars],
                        'Table'[KPI 1]
                    ),
                    'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] )
                        && 'Table'[KPI 1] <> BLANK ()
                )
            RETURN
                SUMX ( Total_Cars_By_Day, 'Table'[Number of cars] )
    )
RETURN
    SUMX (
        FILTER ( Number_Cars, 'Table'[KPI 1] <> BLANK () ),
        'Table'[Number of cars] / [Total_Cars] * 'Table'[KPI 1]
    )

Result in attach PBIX file.

[Anonymous]

Hi @MFelix , this is working like a charm when my KPI is >0%

However when the KPI value is 0% the code is considering the value as blank, and the [Number of cars] is not taken into account in the weighted KPI.

I don't really understand why though because we only specified <> BLANK ()

Any idea ?

EDIT:

I read online that considering 0 as blank was a particularity of the BLANK() expression in DAX. Found what I wanted by replacing 'Table'[KPI 1] <> BLANK () with ISBLANK('Table'[KPI 1])= FALSE()

Glad to have finished this with a final personal contribution 🙂

[MFelix]

Hi @Anonymous ,

Did not realize that you would have 0 values for KPI, glad you were abble to sort it out. 

Other options would be to do the following NOT (ISBLANK('Table'[KPI 1])).

[Anonymous]

Hi @MFelix ,

This is already working fine and successfully implemented on my side.

But to challenge a little further: if we would like to have the same weighting calculation when filtered and selecting more than 1 location,  what would you see in the first field of ISFILTERED?

KPI = IF(ISFILTERED('Table'[Location]), AVERAGE('Table'[KPI 1]), [Number_Cars KPI1])

Idea would be when selecting more than 1 location to be able to have the weighted KPIs on the number of cars for these locations only, and not for all like when nothing is selected and we are displaying [Number_Cars KPI1]

[MFelix]

Hi @Anonymous ,

You want to calculate the number of cars for the selected countries only instead of making it to all the countries in the specific day?

[Anonymous]

Hi @MFelix ,

I meant to say apply Number_Cars KPI1 to filtered location too in the first condition of my KPI  measure on my graph. So that we can select countries and have the same results as in NUmber_Cars KPI1 but with filter

[MFelix]

Hi @Anonymous ,

Sorry for the delay, only have two question about your setup is what do you want to happen if there is more than one value for a country in one day for germany there are 2 values for 12/01 so do you want the average or calculate accordingly to the selected values?

None the less I have setup the following two measures (just some small changes to the previous ones);

Number_Cars KPI1 = 
VAR Number_Cars =
    SUMMARIZE (
        'Table',
        'Table'[Location],
        'Table'[Date],
        'Table'[Number of cars],
        'Table'[KPI 1],
        "Total_Cars",
            VAR Total_Cars_By_Day =
                FILTER (
                    SUMMARIZE (
                        ALLSELECTED(  'Table' ),
                        'Table'[Location],
                        'Table'[Date],
                        'Table'[Number of cars],
                        'Table'[KPI 1]
                    ),
                    'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) && 'Table'[KPI 1] <> BLANK() 
                )
            RETURN
                SUMX ( Total_Cars_By_Day, 'Table'[Number of cars] )
    )
RETURN
    SUMX (FILTER( Number_Cars, 'Table'[KPI 1] <> BLANK()), 'Table'[Number of cars] / [Total_Cars] * 'Table'[KPI 1] )


KPI =
IF (
    ISFILTERED ( 'Table'[Location] ),
    IF (
        HASONEVALUE ( 'Table'[Location] ),
        AVERAGE ( 'Table'[KPI 1] ),
        [Number_Cars KPI1]
    ),
    [Number_Cars KPI1]
)

Check result attach.

[Anonymous]

Hello @MFelix ,

Thank you for the reply.

To answer your question if a location has more than 1 KPI in the same month then the average of the 2 is good (this is what you already specified if I am not wrong).

After re-re-reading the code, and not understanding why it wasn't taking into account the numbers of cars for the selected locations only, I finally noticed that in row 13 of Number_Cars KPI1 we changed ALL('Table') to ALLSELECTED('Table') !

This is working perfectly, thank you for your help and your patience throughout this project. It is providing me a lot of help understanding how to use the language and I will definitely take this home 🙂

Have a good day.