DAX to use calculated measures in ALLEXCEPT filter

[Anonymous]

Hi All,

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

[Anonymous]

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

[v-kelly-msft]

Hi  @Anonymous ,

Is your issue solved now?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[v-kelly-msft · Accepted 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!