Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a requirement to calculate the Average Revenue grouped by Client Vertical, Total Sales Range, and %Contributed Range where Total Sales Range is a calculated measure that checks the values of Total Sales and puts them in the buckets accordingly (0,0-500,000,500,000-1,000,0000 and so on).
Similarly, %Contributed Range is also a calculated measure that checks the values of %Contributed (another measure) and puts them in the buckets (0, 0-0.25,0.25-0.5, and so on).
To calculate Average Revenue , I am trying to use the below DAX:
Average Revenue =
[SUM Revenue] / CALCULATE([Total Clients], ALLEXCEPT(Client, Client[Vertical] ,[Total sales Range],[%Contributed Range]))
But since ALLEXCEPT accepts only Table columns (and not measures), how can I filter the Total Clients with these measures as well?
Expected Output:
here Average Revenue for a Vertical - Not Applicable => SUM Revenue / #Clients for that bucket (Total Sales Range and %Contributed) i.e 8295/4 =2074
Any suggestions would be helpful as to how to filter Total Clients with the measures (Total Sales Range and %Contributed Range).
Thanks
Solved! Go to Solution.
Hi @Anonymous ,
You could first create a table as variable:
var _tab=SUMMARIZE(Client, Client[Vertical] ,"total sales Range",[Total sales Range],"Contributed Range",[%Contributed Range])
Then create a measure as below:
Average Revenue =
var _tab=SUMMARIZE(Client, Client[Vertical] ,"total sales Range",[Total sales Range],"Contributed Range",[%Contributed Range])
Return
[SUM Revenue] / CALCULATE([Total Clients],FILTER(_tab,[Vertical]=MAX([Vertical]))
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi @Anonymous ,
Is your issue solved now?
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi @Anonymous ,
You could first create a table as variable:
var _tab=SUMMARIZE(Client, Client[Vertical] ,"total sales Range",[Total sales Range],"Contributed Range",[%Contributed Range])
Then create a measure as below:
Average Revenue =
var _tab=SUMMARIZE(Client, Client[Vertical] ,"total sales Range",[Total sales Range],"Contributed Range",[%Contributed Range])
Return
[SUM Revenue] / CALCULATE([Total Clients],FILTER(_tab,[Vertical]=MAX([Vertical]))
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
79 | |
68 | |
61 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
70 |