Using ALLSELECTED in Divide function

[MichaelaMul]

Hi everyone! I made the following measures to get the volume share of each category in a specific brand in my dataset. The two top measures are working properly, with the category measure filtering properly when selected. However, when I combine the two measures to divide it the ALLSELECTED measure stops working. What am I doing wrong?

Total for Category = 
CALCULATE (SUM('DATA'[Volume]), 'DATA'[Brand] = "ABC", ALLSELECTED('CategoryTable'[Category]))

Total for Brand = 
CALCULATE (SUM('DATA'[Volume]), 'DATA'[Brand] = "ABC")

Total for Category divided by Total for Brand =
DIVIDE([Total for Category], [Total for Brand])

Thanks!

[Anonymous]

Hi @MichaelaMul ,

Thanks for the reply from lbendlin .

I do not really know what kind of desired effect you want.

What do SUM('DATA'[CY Reforecast] and SUM('DATA'[Volume]) mean respectively? Why do you divide them?

I would appreciate it if you could provide me with sample data and a diagram of the desired effect, please remove any sensitive data in advance.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[MichaelaMul]

Hi thanks for the reply! I made a typo and updated the formulas above. Here's the sample data.

CategoryTable:

Product Group	Category
LIGHT SOUR CREAM	SOUR CREAM
SOUR CREAM	SOUR CREAM
SKIM MILK	MILK
WHOLE MILK	MILK
1% MILK	MILK
2% MILK	MILK
ORANGE JUICE	DRINKS
CREAMER	CREAMER
NON-DAIRY CREAMER	CREAMER
LIGHT CREAM	CREAMER
SPRING WATER	DRINKS

DATA table:

Store	Brand	Product Group	Time Period	Volume
STORE A	ABC	LIGHT SOUR CREAM	March	12,525
STORE A	ABC	LIGHT SOUR CREAM	February	11,340
STORE A	ABC	LIGHT SOUR CREAM	January	9,720
STORE A	ABC	SOUR CREAM	March	42,134
STORE A	ABC	SOUR CREAM	February	35,280
STORE A	ABC	SOUR CREAM	January	26,880
STORE A	ABC	SKIM MILK	March	1,645
STORE A	ABC	SKIM MILK	February	1,476
STORE A	ABC	SKIM MILK	January	1,684
STORE A	ABC	SKIM MILK	March	5,300
STORE A	ABC	WHOLE MILK	February	5,157
STORE A	ABC	WHOLE MILK	January	5,711
STORE A	ABC	WHOLE MILK	March	7,900
STORE A	ABC	WHOLE MILK	February	7,408
STORE A	ABC	WHOLE MILK	January	8,632
STORE A	ABC	WHOLE MILK	February	3
STORE A	ABC	SKIM MILK	January	4
STORE A	ABC	SKIM MILK	March	711
STORE A	ABC	SKIM MILK	February	667
STORE A	ABC	SKIM MILK	January	712
STORE A	ABC	ORANGE JUICE	March	65
STORE A	ABC	ORANGE JUICE	February	59
STORE A	ABC	ORANGE JUICE	January	95
STORE A	ABC	ORANGE JUICE	March	154
STORE A	ABC	2% MILK	February	136
STORE A	ABC	2% MILK	January	148
STORE A	ABC	2% MILK	March	228
STORE A	ABC	2% MILK	February	212
STORE A	ABC	2% MILK	January	226
STORE A	ABC	CREAMER	March	87
STORE A	ABC	CREAMER	February	105
STORE A	ABC	CREAMER	January	102
STORE A	ABC	NON-DAIRY CREAMER	March	8,237
STORE A	ABC	NON-DAIRY CREAMER	February	7,440
STORE A	ABC	NON-DAIRY CREAMER	January	3,360

Thanks!

[lbendlin]

Like this?

[MichaelaMul]

Yes! The sample data doesn't show it but there are other brands and stores in the table, so I can't just total the column.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[MichaelaMul]

Store	Brand	Product Group	Time Period	Volume
STORE A	ABC	LIGHT SOUR CREAM	March	12,525
STORE A	ABC	LIGHT SOUR CREAM	February	11,340
STORE A	ABC	LIGHT SOUR CREAM	January	9,720
STORE A	ABC	SOUR CREAM	March	42,134
STORE A	ABC	SOUR CREAM	February	35,280
STORE A	ABC	SOUR CREAM	January	26,880
STORE A	ABC	SKIM MILK	March	1,645
STORE A	ABC	SKIM MILK	February	1,476
STORE A	ABC	SKIM MILK	January	1,684
STORE A	ABC	SKIM MILK	March	5,300
STORE A	ABC	WHOLE MILK	February	5,157
STORE A	ABC	WHOLE MILK	January	5,711
STORE A	ABC	WHOLE MILK	March	7,900
STORE A	ABC	WHOLE MILK	February	7,408
STORE A	ABC	WHOLE MILK	January	8,632
STORE A	ABC	WHOLE MILK	February	3
STORE B	ABC	SKIM MILK	January	4
STORE B	ABC	SKIM MILK	March	711
STORE B	ABC	SKIM MILK	February	667
STORE B	ABC	SKIM MILK	January	712
STORE B	ABC	ORANGE JUICE	March	65
STORE B	ABC	ORANGE JUICE	February	59
STORE B	ABC	ORANGE JUICE	January	95
STORE B	ABC	ORANGE JUICE	March	154
STORE B	ABC	2% MILK	February	136
STORE B	ABC	2% MILK	January	148
STORE B	ABC	2% MILK	March	228
STORE B	ABC	2% MILK	February	212
STORE B	ABC	2% MILK	January	226
STORE B	ABC	CREAMER	March	87
STORE B	ABC	CREAMER	February	105
STORE B	ABC	CREAMER	January	102
STORE B	ABC	NON-DAIRY CREAMER	March	8,237
STORE B	ABC	NON-DAIRY CREAMER	February	7,440
STORE B	ABC	NON-DAIRY CREAMER	January	3,360
STORE A	DEF	LIGHT SOUR CREAM	March	12,565
STORE A	DEF	LIGHT SOUR CREAM	February	5,549
STORE A	DEF	LIGHT SOUR CREAM	January	556
STORE A	DEF	SOUR CREAM	March	466
STORE A	DEF	SOUR CREAM	February	225
STORE A	DEF	SOUR CREAM	January	852
STORE A	DEF	SKIM MILK	March	456
STORE A	DEF	SKIM MILK	February	147
STORE A	DEF	SKIM MILK	January	3,654
STORE A	DEF	SKIM MILK	March	458
STORE A	DEF	WHOLE MILK	February	4,578
STORE A	DEF	WHOLE MILK	January	158
STORE A	DEF	WHOLE MILK	March	5,578
STORE A	DEF	WHOLE MILK	February	23
STORE A	DEF	WHOLE MILK	January	258
STORE A	DEF	WHOLE MILK	February	78
STORE B	DEF	SKIM MILK	January	954
STORE B	DEF	SKIM MILK	March	146
STORE B	DEF	SKIM MILK	February	357
STORE B	DEF	SKIM MILK	January	22
STORE B	DEF	ORANGE JUICE	March	5
STORE B	DEF	ORANGE JUICE	February	142
STORE B	DEF	ORANGE JUICE	January	35
STORE B	DEF	ORANGE JUICE	March	25
STORE B	DEF	2% MILK	February	41
STORE B	DEF	2% MILK	January	29
STORE B	DEF	2% MILK	March	596
STORE B	DEF	2% MILK	February	256
STORE B	DEF	2% MILK	January	45
STORE B	DEF	CREAMER	March	78
STORE B	DEF	CREAMER	February	87
STORE B	DEF	CREAMER	January	96
STORE B	DEF	NON-DAIRY CREAMER	March	14
STORE B	DEF	NON-DAIRY CREAMER	February	22
STORE B	DEF	NON-DAIRY CREAMER	January	33

Expected result

Store A
Total ABC for Store A	182,795	100%
Category
Sour Cream	137,879	75.4%
Milk	44,916	24.6%
Store B
Total ABC for Store B	22,748	100%
Category
Milk	3,044	13.4%

[lbendlin · Accepted solution]

Still no coding required.

[MichaelaMul]

Thank you! Do you know of a way to show each category as a card instead of within a table?

[lbendlin]

will all cards be subject to the same filter?

[MichaelaMul]

All cards I want to filter - to see the same Store and Brand but each card would have a different category. For example:

1 card:  shows the % of milk/ overall Volume for Brand ABC in store A = 27.6%

2 card:  shows the % of sour cream/ overall Volume for Brand ABC in store A = 72.4%

[lbendlin]

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.