Measure on a measure ?

I would like to display the % of customers having ordered a number of different brand : 1 brand - 45% 2 brands - 20% 3 brands - 17% 4 brands - 5% 5 brands - 3% Ideally, just 4 lines, with...

Need Help

v-juanli-msft

7 years ago

Hi François

Assume you table is like

First create two calculated columns

discount = CALCULATE(DISTINCTCOUNT(Sheet2[brand]),ALLEXCEPT(Sheet2,Sheet2[cust]))

category = SWITCH(TRUE(),[discount]=1,"1 brand ordered",[discount]=2,"2 brands ordered",[discount]=3,"3 brands ordered",[discount]>=4,"4 or More Brand Ordered")

Then create measures as below

count per brand number = CALCULATE(DISTINCTCOUNT(Sheet2[cust]),FILTER(ALL(Sheet2),[discount]=MAX([discount])))

total cust = CALCULATE(DISTINCTCOUNT(Sheet2[cust]),ALL(Sheet2))

percent = [count per brand number]/[total cust] 

total value = CALCULATE(SUM(Sheet2[value]),FILTER(ALL(Sheet2),[discount]=MAX([discount])))

Then add [category], [percent],[total value] in the table visual

Best Regards

Maggie

discount = CALCULATE(DISTINCTCOUNT(Sheet2[brand]),ALLEXCEPT(Sheet2,Sheet2[cust])) category = SWITCH(TRUE(),[discount]=1,"1 brand ordered",[discount]=2,"2 brands ordered",[discount]=3,"3 brands ordered",[discount]>=4,"4 or More Brand Ordered")

count per brand number = CALCULATE(DISTINCTCOUNT(Sheet2[cust]),FILTER(ALL(Sheet2),[discount]=MAX([discount]))) total cust = CALCULATE(DISTINCTCOUNT(Sheet2[cust]),ALL(Sheet2)) percent = [count per brand number]/[total cust] total value = CALCULATE(SUM(Sheet2[value]),FILTER(ALL(Sheet2),[discount]=MAX([discount])))

Forum Discussion

Measure on a measure ?

Related content

Measure

DAX Measure

Path Measure

% of total measure

Count Measure