Measure on a measure ?

[François]

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 !

[François]

Argghh. How do I answer to this thread ?? 🙂 

[François]

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 !"

[v-juanli-msft · Accepted solution]

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

[PattemManohar]

@François Could you please post the sample data to suggest any solution.

[François]

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]

@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]

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]

@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 % )