Average formula not working at different slicer level

[Vinh2003]

Hello,

I have some sales data for 10 Sales offices and these offices roll up to 2 Sales regions.  I used the below Average formula to get the average monthly sales for each of these offices.  In our report, we have 2 slicers, 1 for the Sales office and the other for the Sales region.  When the Sales Office slicer is selected on a specific office, the average Sales number is correct.  However, when none of the Sales office is selected on the slicer and the slicer is only selected on the Sales region, the average Sales number is not correct.

Average Sales = CALCULATE(AVERAGE(data[Sales]), ALLSELECTED(data))

I was told possibly that the error is not in this DAX formula but rather my modeling in that it is not set up in a star schema set up.  Any advice here?

Thank you

[littlemojopuppy]

Hi @Vinh2003 hard to say.  Can you provide some sample data to play with?

[Vinh2003]

Hello, I wanted to send a bpix file to show the behavior of the slicer but I am not able to send a file on this board as I don't see an option to do this?  thanks

[Ashish_Mathur]

Hi,

Upload the file to a file shring service (Google Drive) and share the download link here.  Show the problem there very clearly and also the expected result.

[Vinh2003]

Hello,

I saved the pbix file with dummy data on googledrive in the link below...

https://drive.google.com/file/d/1gA6CRL4cJUXSZjdlHEl1s0Ja4e3_LKPr/view?usp=sharing

Correct behavior when any item in the 'Office' slicer is checked...

However, when no item is checked in the 'Office' slicer, the average orange and gray lines are too low vs. the actual value (blue bars).  All of the Offices field roll up into the 2 Business Groups.

Any insight appreciated.

[littlemojopuppy]

Hi @Vinh2003 

You don't actually have any measure defined for Goal.  And of the 3,339 records in your data table, only 240 have a value that is not zero or BLANK().  So based on that, it's at least mathematically correct.

How should it be calculated?

[Vinh2003]

I cleaned up the data to remove the zero and blank records.  The chart and slicer still has the issue where if none of the 'Sales Office' items are selected, the 'Average' lines (black and yellow) are much too low.  These 'Average' lines should be close to the top of the blue bars.  All of the 'Sales Office' rolls up into the 2 'Business Group' item.  It is probably due to my 'Average' DAX formula but not sure how to fix it.

Link to the updated 'Demo data' pbix file is at:  https://drive.google.com/file/d/1nZwNzUG1WJB1oWQxshMlMlNr2Kj2HaaS/view?usp=sharing

Thank you.