Forum Discussion

François's avatar
François
Icon for Helper I rankHelper I
7 years ago
Solved

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's avatar
    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

François's avatar
François
Icon for Helper I rankHelper I
7 years ago

Argghh. How do I answer to this thread ?? :) 

François's avatar
François
Icon for Helper I rankHelper I
7 years ago

My three previous messages didn't appear, I don't know why. Let's try to copy paste :

 

"I'm really having difficulties to answer to this thread :)

 

Third attempt to answer: 

 

thanks a lot for your solutions, both work ! It's fun to see how each problem can be multiple solutions. It takes me some time to adapt to BI, it's a powerful tool and such a change from my old tools. Thanks again, it's very useful !"