Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to use a DAX command to identify monthly totals that are above the monthly average and use conditional formating to highlight those which are above the average. Current commands used:
- Total = SUM(sheet1[Number Sold])
- Average = AVERAGEX(VALUES(Sheet1[Date]),[Total])
Have attempted to use:
- Measure = IF([Total]>[Average], "Red", "Green")
However I am not getting the desired result and it does not identify the months where the total is above the monthly average per category.
Hi,
I am not sure how your data model looks like, but please try to fix the AVERAGE MEASURE something like below whether it suits your requirement.
Average measure: =
AVERAGEX ( ALLSELECTED ( Sheet1[monthname], Sheet1[monthname_sort] ), [Total] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim that worked to get me the correct average calulation however it is still not working for the measure comparing the monthly totals per category to the average to identify those which are above the average.
For example from the original screen shot, for "Site A" the average is 3.67 therefore the months where the number sold were greater than the average = January, Feburary, May, July, August and September and highlight these values Red
Hi,
It is hard to tell without understanding how the data model looks like, but I think, in that case, the average measure has to remove filers that are applied except [Site] column.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Data is very basic:
Hi,
Thank you for sharing.
Do you mean something like below?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
57 | |
21 | |
19 | |
19 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |