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 a "4 brands and more" as the final one.

I can calculate a measure for the number of brand per account,  but I don't know how to use it to calculate the % of account for each result. Any help would be greatly appreciated :) Thank you !

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

8 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    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

      Sure, here is a simple example : 

       

      Data I have :

      Cust1 Brand1 Item1 10€

      Cust1 Brand1 Item2 5€

      Cust1 Brand2 Item3 15€

      Cust2 Brand 1 Item1 10€

      Cust3 Brand 2 Item3 15€

       

      1 customer ordered 2 different brands

      2 customers ordered just 1 brand

       

      So the expected result is :

       

      1 brand ordered - 66% of customers - total TO 25€

      2 brands ordered - 33% of customers - total TO 30€

       

       

      • PattemManohar's avatar
        PattemManohar
        Icon for Community Champion rankCommunity Champion
        7 years ago
        François Do you want to calculate number of distinct brands ordered like 1 Brand order or 2 Brands Ordered etc... or percentage of customers ordered each brand. As there was a difference(confusion) from your initial post to latest post.
    • 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 !"