Calculate Total Revenue per Customer then group it per Bands

DAX DAX Calculations
1

I want to be able to get the total revenue per customer and use that value to group their bands and also count how many customers are in that band and how much total there is per band. This is the data that I have.
image

and this is the output that I want.

image
image1177×152 8.67 KB

My problem is, I can’t do the sum per customer on a column. If it’s measure I am not able to use it as an X-Axis. I tried the grouping, but can’t seem to figure out the problem.

Here’s the measure that I tried.
**Total = VAR TOT = SUMX(DISTINCT(D365_Fees[_bfp_accountname_value]),[Total])
RETURN
CALCULATE([Total],
FILTER(VALUES(D365_Fees),
COUNTROWS(
FILTER(‘Grouped Revenue Bands’,
TOT > ‘Grouped Revenue Bands’[From]
&& TOT <= ‘Grouped Revenue Bands’[To]))
> 0 ))

Thank you for your help.

2

I think I just solved it. :joy:

**Total =
CALCULATE([Total],
FILTER(VALUES(D365_Accounts),
COUNTROWS(
FILTER(‘Grouped Revenue Bands’,
[Total] > ‘Grouped Revenue Bands’[From]
&& [Total] <= ‘Grouped Revenue Bands’[To]))
> 0 ))

This is what I used to count the Customers/Accounts
**No. of Accounts =
CALCULATE([No. of Accounts],
FILTER(VALUES(D365_Accounts),
COUNTROWS(
FILTER(‘Grouped Revenue Bands’,
[Total] > ‘Grouped Revenue Bands’[From]
&& [Total] <= ‘Grouped Revenue Bands’[To]))
> 0 ))