topic Re: Average of group not showing correctly in DAX Commands and Tips

Average of group not showing correctly

Anonymous — Thu, 19 Dec 2024 14:30:05 GMT

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

Re: Average of group not showing correctly

mark_endicott — Thu, 19 Dec 2024 15:46:38 GMT

Anonymous - please provide the DAX you have already used.

Re: Average of group not showing correctly

Anonymous — Fri, 20 Dec 2024 14:43:08 GMT

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)'))

Re: Average of group not showing correctly

mark_endicott — Fri, 20 Dec 2024 14:53:56 GMT

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.

Re: Average of group not showing correctly

mark_endicott — Mon, 23 Dec 2024 10:55:28 GMT

Anonymous - 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!

Re: Average of group not showing correctly

Anonymous — Thu, 26 Dec 2024 14:27:03 GMT

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!

Re: Average of group not showing correctly

pcoley — Thu, 26 Dec 2024 15:56:33 GMT

Anonymous
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😀

Re: Average of group not showing correctly

Anonymous — Fri, 27 Dec 2024 13:53:00 GMT

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.

Re: Average of group not showing correctly

pcoley — Fri, 27 Dec 2024 14:25:18 GMT

Anonymous
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?

Re: Average of group not showing correctly

Anonymous — Mon, 30 Dec 2024 15:04:48 GMT

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

Re: Average of group not showing correctly

pcoley — Mon, 30 Dec 2024 19:10:45 GMT

Anonymous

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😁

Re: Average of group not showing correctly

Anonymous — Tue, 31 Dec 2024 08:01:55 GMT

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

Hi Anonymous ,

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