Forum Discussion
Solved
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 p...
Still no coding required.
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?
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.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.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%