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

Sorry if it was not clear :

 

we want to calculate how many of our customers are ordering how many different brands : 

 

how many are ordering only 1 disctinct brand ? 

How many are ordering 2 different brands ?

How many are ordering 3 different brands ?

How many are ordering 4 and more different brands ?

 

Result as a % : 65% are ordering only one brand, 23% are ordering 2 brands, 12% are ordering 3 brands, etc... 

PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

François Please try this as a "New Table"

 

Test102Output = 
ADDCOLUMNS(
    SUMMARIZE(Test102CountofCount,Test102CountofCount[Customer],"Cnt",DISTINCTCOUNT(Test102CountofCount[Brand])),
    "Flag",SWITCH(TRUE(),[Cnt]=1,"1 Brand Ordered",
                        [Cnt]=2,"2 Brand Ordered",
                        [Cnt]=3,"3 Brand Ordered",
                        [Cnt]>3,"4 or More Brand Ordered")
        )

 

You can then use the Flag and Cnt fields to visual as you like (to show in % )