Forum Discussion
Using ALLSELECTED in Divide function
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!
Still no coding required.
12 Replies
You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.
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 TeamIf 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!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!
Like this?
