Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have values for each category, sub-category by month. What i want to create is a measure (or some other way) to show values by category by Calendar quarter - but the values need to be SUMed up at category level and AVERAGED over calendar months in that quarter.
month Cat subcat value
1/1/2018 | AA | pp | 290 |
1/1/2018 | AA | 35 | |
1/1/2018 | AA | rr | 589 |
2/1/2018 | AA | rr | 987 |
2/1/2018 | AA | 149 | |
2/1/2018 | AA | pp | 520 |
3/1/2018 | AA | rr | 863 |
3/1/2018 | AA | 33 | |
3/1/2018 | AA | pp | 195 |
Sum of AA in Jan = 914
Sum of AA in feb = 1656
Sum of AA in mar = 1091
I want to show 1220 as Q1 average. But power BI is showing average of all 9 values as 406.7.
Any help?
thanks
Nidhi
Solved! Go to Solution.
Hi @nidhishah ,
Not sure if you are using a measure or a simple average from the column but in DAX the values are calculated based on context so when you add all values to your visual you are getting the average of the lines has one data set in this case you want to have the sum of the averages so you need to create a SUMX measure:
Average Total = VAR average_values = AVERAGE ( Table1[Value] ) RETURN IF ( HASONEFILTER ( Table1[Month] ); average_values; SUMX ( ALLSELECTED ( Table1[Month] ); average_values ) )
As you can see below on the several visuals you can see that if I add the month to the visual you get 914 and so on otherwise you get the 1220.
Be aware that if you want to add the subcategory or some other columns this measure need to be changed.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @nidhishah ,
Not sure if you are using a measure or a simple average from the column but in DAX the values are calculated based on context so when you add all values to your visual you are getting the average of the lines has one data set in this case you want to have the sum of the averages so you need to create a SUMX measure:
Average Total = VAR average_values = AVERAGE ( Table1[Value] ) RETURN IF ( HASONEFILTER ( Table1[Month] ); average_values; SUMX ( ALLSELECTED ( Table1[Month] ); average_values ) )
As you can see below on the several visuals you can see that if I add the month to the visual you get 914 and so on otherwise you get the 1220.
Be aware that if you want to add the subcategory or some other columns this measure need to be changed.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português