Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

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

Itemsmonth_yearwindowtypeStore codemaxdate_month
2202102abcA12011
2202102abc-2A12011
2202102lmnA12011
2202102xyzA12011
3202102mnoA26031
6202102abcB26031
4202102lmnB26031
3202102mnoA26131
5202102abcB26131
5202102lmnA26131
1202102abcA26221
1202102lmnA26221
1202102xyzB26221
2202102mnoA26661
7202102abcA26661
6202102lmnA26661
3202102xyzA26661
1202102mnoA26721
4202102abcB26721
4202102abc-2A26721
5202102lmnA26721
7202102xyzA26721
1202102mnoB27381
3202102abcB27381
4202102lmnA27381
1202102xyzA27381
4202102mnoB28321
4202102abcA28321
4202102abc-2A28321
6202102lmnA28321
1202102abcA28401
1202102lmnA28401
1202102abcA28801
1202102lmnA28801
4202102mnoB28951
0202102abcA28951
2202102mnoA99991
8202102abcA99991
4202102abc-2A99991
4202102lmnA99991
0202102xyzA99991


Output :

Items : Sum of items (Summed up by each Store code , dynmically change as per selection of type.)
Items will be distributed as :

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 ItemsNO 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.



2 ACCEPTED SOLUTIONS
PaulDBrown
Community Champion
Community Champion

Sorry, I'm finding it hard to understand what you are after. Can you elaborate and show a depiction of the expected outcome?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

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 )

with %.jpg

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
PaulDBrown
Community Champion
Community Champion

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

Threshold.jpg

Leave it unrelated in the model:
model.jpg

 

 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

Threshold.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

File Link 

Hey @PaulDBrown  please see the file here for reference.

PaulDBrown
Community Champion
Community Champion

Sorry, I'm finding it hard to understand what you are after. Can you elaborate and show a depiction of the expected outcome?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

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.

flav15_0-1649142057924.png

 

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 )

with %.jpg

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Hey @PaulDBrown  

Thanks !! Once the total is correct the % will automatically fall into place. 
Appreciate your help.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.