Average of group not showing correctly

[mbmcdonald2]

Hi All,

I'm having some trouble with a calculation here as the "total" in the below visual does not represent the average of what is presented, which is what I would like. Essentially I would like to take an average of what is in the "Consumer Preference Score" column and divide each consumer preference score by that average to get the "Consumer Preference Index". As you can see, right now it does not look like it's being calculated correctly. 

Any assistance would be appreciated!

Thanks,

Maggie

[v-zhouwen-msft]

Hi all,thanks for the quick reply, I'll add more.

Hi @mbmcdonald2 ,

Try this

Measure = 
VAR _selection = SELECTEDVALUE('Field Select Parameter'[Type])
VAR _table1 = ADDCOLUMNS(ALL('Product Hierarchy'[Category]),"Result",[Consumer Preference Score])
VAR _table2 = ADDCOLUMNS(ALL('Product Hierarchy'[Sub-Category]),"Result",[Consumer Preference Score])
RETURN
SWITCH(TRUE(),
_selection = "Category",DIVIDE([Consumer Preference Score],AVERAGEX(_table1,[Result])),
_selection = "Sub-Category",DIVIDE([Consumer Preference Score],AVERAGEX(_table2,[Result]))
)

Final output

Best Regards

[pcoley]

@mbmcdonald2 
We don´t know much about your model and the context where the measure is executed.
Therefore it´s very hard to develop a measure that works with your model and report.

Can you share the pbix? 

[mbmcdonald2]

Hi @pcoley - completely understand.

Here is the PBIX: https://drive.google.com/file/d/1MEiWn62BLC5gDQNDsg0Ti3UyQ26WP2zA/view?usp=sharing 

Please let me know if you have any issues with access. Thanks! Maggie

[pcoley]

@mbmcdonald2 

Please try with this measure:

Avg. Consumer Preference Score = 
SWITCH(values('Field Select Parameter'[Selection Parameter Order]),
0 ,CALCULATE ([Consumer Preference Score], ALLSELECTED('Product Hierarchy'[Category])),
1,CALCULATE ([Consumer Preference Score], ALLSELECTED('Product Hierarchy'[Sub-Category])))

I hope this helps, if so please accept the solution.
👉kudos are welcome😁

[mark_endicott]

@mbmcdonald2 - did we resolve your issue? If we did, please select the appropriate solution, it helps with visibility for others and for SuperUsers to keep their status! Thanks!

[mark_endicott]

@mbmcdonald2 - please provide the DAX you have already used. 

[mbmcdonald2]

Yes, so Consumer Preference Score is this: 

Consumer Preference Score = [Industry Per Caps Rank]+[Observed Drinkers Rank] -- as you can see it's the addition of two rank figures. 

The Average Consumer Preference Score is this:

Avg. Consumer Preference Score =

CALCULATE ([Consumer Preference Score], ALLSELECTED('Nielsen (excl dist)'))

[mark_endicott]

Ok, if you want an average of the scores in the visual you should use:

AVERAGEX( ALLSELECTED('Nielsen (excl dist)'), [Consumer Preference Score])

If this works, please accept as the solution. 

[mbmcdonald2]

Hi @mark_endicott 

Thanks so much for getting back to me. It looks like when I take this approach I get the following: 

In this instance, I would expect the average to be ~16. The sub-category column being used comes from a parameter. Any help would be appreciated!

[pcoley]

@mbmcdonald2 
Please try with the next options:
-->Please change in the 1st code "TransactionTable" with the name of your transactionTable
To calculated the average of the subcaategories including the total please use the next measure:

Avg. Consumer Preference Score :=
AVERAGEX(
    VALUES( 'Nielsen (excl dist)' ),
    CALCULATE(
        AVERAGEX(
            TransactionTable, //please place here your transactionTable
            [Consumer Preference Score]
        )
    )
)

 If you want to have all the selected one please use this measure:

MeasureALLSELECTED =
CALCULATE(
    [Avg. Consumer Preference Score],
    ALLSELECTED( 'Nielsen (excl dist)' )
)

 and if you want to have all the subcategories no matter if they are selected please use this measure:

MeasureALL =
CALCULATE(
    [Avg. Consumer Preference Score],
    ALL( 'Nielsen (excl dist)' )
)

I hope this help if so pleace mark as a solution. kudos are welcome😀

[mbmcdonald2]

Hi @pcoley 

Thank you very much for your input. With this solution I get the following (see screenshot). The average figure I would expect would be 16.