Forum Discussion
Dynamic sum of column
Hi All,
Here is a dataset .
Details :
Items : No of items per window name.
window : Name of window
type : window type
Store code : Code of store where that window & item is present
maxdate_month: Ignore
| Items | month_year | window | type | Store code | maxdate_month |
| 2 | 202102 | abc | A | 1201 | 1 |
| 2 | 202102 | abc-2 | A | 1201 | 1 |
| 2 | 202102 | lmn | A | 1201 | 1 |
| 2 | 202102 | xyz | A | 1201 | 1 |
| 3 | 202102 | mno | A | 2603 | 1 |
| 6 | 202102 | abc | B | 2603 | 1 |
| 4 | 202102 | lmn | B | 2603 | 1 |
| 3 | 202102 | mno | A | 2613 | 1 |
| 5 | 202102 | abc | B | 2613 | 1 |
| 5 | 202102 | lmn | A | 2613 | 1 |
| 1 | 202102 | abc | A | 2622 | 1 |
| 1 | 202102 | lmn | A | 2622 | 1 |
| 1 | 202102 | xyz | B | 2622 | 1 |
| 2 | 202102 | mno | A | 2666 | 1 |
| 7 | 202102 | abc | A | 2666 | 1 |
| 6 | 202102 | lmn | A | 2666 | 1 |
| 3 | 202102 | xyz | A | 2666 | 1 |
| 1 | 202102 | mno | A | 2672 | 1 |
| 4 | 202102 | abc | B | 2672 | 1 |
| 4 | 202102 | abc-2 | A | 2672 | 1 |
| 5 | 202102 | lmn | A | 2672 | 1 |
| 7 | 202102 | xyz | A | 2672 | 1 |
| 1 | 202102 | mno | B | 2738 | 1 |
| 3 | 202102 | abc | B | 2738 | 1 |
| 4 | 202102 | lmn | A | 2738 | 1 |
| 1 | 202102 | xyz | A | 2738 | 1 |
| 4 | 202102 | mno | B | 2832 | 1 |
| 4 | 202102 | abc | A | 2832 | 1 |
| 4 | 202102 | abc-2 | A | 2832 | 1 |
| 6 | 202102 | lmn | A | 2832 | 1 |
| 1 | 202102 | abc | A | 2840 | 1 |
| 1 | 202102 | lmn | A | 2840 | 1 |
| 1 | 202102 | abc | A | 2880 | 1 |
| 1 | 202102 | lmn | A | 2880 | 1 |
| 4 | 202102 | mno | B | 2895 | 1 |
| 0 | 202102 | abc | A | 2895 | 1 |
| 2 | 202102 | mno | A | 9999 | 1 |
| 8 | 202102 | abc | A | 9999 | 1 |
| 4 | 202102 | abc-2 | A | 9999 | 1 |
| 4 | 202102 | lmn | A | 9999 | 1 |
| 0 | 202102 | xyz | A | 9999 | 1 |
Output :
Items : Sum of items (Summed up by each Store code , dynmically change as per selection of type.)
Items will be distributed as :
0
1
2
>=3
Count of (Distinct)Store Code.
Case 1: If no filter is selected then , sum no of items grouped by store code.
If type is filtered , sum of shelves will be affected .
PS : It is not count of store based on item column.
| Sum of Items | NO of Stores(Distinct) | % share of column no of stores |
0 | __ | |
| 1 | __ | |
| 2 | ___ | |
| >=3 |
It's not how many stores have 0 items , 1 items or so on.
Sorry, I'm finding it hard to understand what you are after. Can you elaborate and show a depiction of the expected outcome?
Sure. To get the total correct, change the measure to:
Store by Threshold = VAR stores = VALUES ( Sheet1[Store code] ) RETURN SUMX ( 'Item Threshold', CALCULATE ( IF ( MAX ( 'Item Threshold'[Order] ) < 3, COUNTROWS ( FILTER ( stores, [Sum Items] = SELECTEDVALUE ( 'Item Threshold'[Order] ) ) ), COUNTROWS ( FILTER ( stores, [Sum Items] > 2 ) ) ) ) )For the % over the total, use:
% over total = VAR _total = CALCULATE ( [Store by Threshold], ALL ( 'Item Threshold' ) ) RETURN DIVIDE ( [Store by Threshold], _total )
6 Replies
PaulDBrownCommunity Champion
Sorry, I'm finding it hard to understand what you are after. Can you elaborate and show a depiction of the expected outcome?
Hey PaulDBrown This is almost what is needed. Appriciate the help !!
Can you help me to calculate the % share of the threshold.
As % of stores having 0 items out of total x = a%
and so on. Currently the sum by threshold takes the >=3 value as always the total which isnt' logically correct.- PaulDBrown
Sure. To get the total correct, change the measure to:
Store by Threshold = VAR stores = VALUES ( Sheet1[Store code] ) RETURN SUMX ( 'Item Threshold', CALCULATE ( IF ( MAX ( 'Item Threshold'[Order] ) < 3, COUNTROWS ( FILTER ( stores, [Sum Items] = SELECTEDVALUE ( 'Item Threshold'[Order] ) ) ), COUNTROWS ( FILTER ( stores, [Sum Items] > 2 ) ) ) ) )For the % over the total, use:
% over total = VAR _total = CALCULATE ( [Store by Threshold], ALL ( 'Item Threshold' ) ) RETURN DIVIDE ( [Store by Threshold], _total )
File Link
Hey PaulDBrown please see the file here for reference.- PaulDBrown
Anonymous Thanks for the file
Ok, see if this is what you need.
First create a table for the thresholds using the "Enter Data" under Home in the ribbon:
Leave it unrelated in the model:
Then create the measures:
Sum Items = SUM(Sheet1[Items])Store by Threshold = VAR stores = VALUES ( Sheet1[Store code] ) RETURN IF ( MAX ( 'Item Threshold'[Order] ) < 3, COUNTROWS ( FILTER ( stores, [Sum Items] = SELECTEDVALUE ( 'Item Threshold'[Order] ) ) ), COUNTROWS ( FILTER ( stores, [Sum Items] > 2 ) ) )To get