Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
100 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |