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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hashari
Helper I
Helper I

How to Calculate Subtotal of Other Rows (Exclude Current Row Value)

I am looking for a way to generate a subtotal column that's the sum of other rows of data, so in other words, it ignores / excludes the current row's value from the subtotal.

 

  • Per the screenshot below, I want to create what's displayed in col D (metric1_subtotal_other rows).  
    • Col E contains examples of how I am arriving at the subtotals I would like to create
  • I already have cols A-C in my PBI report.
 
 

Trend_test_ss.JPG

 

In case it's needed, you can access the sample PBI report here:

https://drive.google.com/file/d/1vGtN2pr8zHD_MSxANbs63KMcueOTcUPz/view?usp=sharing

 

Also, here is a link to the excel file used to create the PBI file and screenshot above:  https://drive.google.com/file/d/19spH03FeAMPh6SWE2zTYqFsSy8cWeHl8/view?usp=sharing

1 ACCEPTED SOLUTION

@hasharitry this

 

_test = 
IF (
    OR (
        OR ( ISINSCOPE ( SM[srg] ), ISINSCOPE ( SM[sds] ) ),
        ISINSCOPE ( SM[loc] )
    ),
    CALCULATE ( SUM ( CS1[metric1_subtotal] ), ALLSELECTED ( CS1 ) )
        - SUMX ( CS1, CS1[metric1_subtotal] ),
    IF (
        HASONEVALUE ( SM[sdv] ),
        VAR _1 =
            MAX ( SM[sdv] )
        VAR _2 =
            CALCULATE (
                SUMX ( CS1, CS1[metric1_subtotal] ),
                FILTER ( ALLSELECTED ( SM ), SM[sdv] <> _1 )
            )
        RETURN
            _2,
        SUMX ( CS1, CS1[metric1_subtotal] )
    )
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

6 REPLIES 6
smpa01
Super User
Super User

@hasharican you try this as a measure?

 

_x := 
IF (
    HASONEVALUE ( SM[sdv] ),
    VAR _1 =
        MAX ( SM[sdv] )
    VAR _2 =
        CALCULATE (
            SUMX ( CS1, CS1[metric1_subtotal] ),
            FILTER ( ALLSELECTED ( SM ), SM[sdv] <> _1 )
        )
    RETURN
        _2,
    SUMX ( CS1, CS1[metric1_subtotal] )
)

 Capture.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@smpa01  - Thanks for sharing this approach!

It seems to work fine at the 'sdv' level (Level-1), but when i drill down to 'srg' (Level-2), it doesn't seem to follow the drill-level rows.

 

hashari_0-1614792978559.png

 

Thoughts?

@hashari  try this and see if it works as you desired

 

Measure :=
IF (
    OR (
        OR ( ISINSCOPE ( SM[srg] ), ISINSCOPE ( SM[sds] ) ),
        ISINSCOPE ( SM[loc] )
    ),
    SUMX ( CS1, CS1[metric1_subtotal] ),
    IF (
        HASONEVALUE ( SM[sdv] ),
        VAR _1 =
            MAX ( SM[sdv] )
        VAR _2 =
            CALCULATE (
                SUMX ( CS1, CS1[metric1_subtotal] ),
                FILTER ( ALLSELECTED ( SM ), SM[sdv] <> _1 )
            )
        RETURN
            _2,
        SUMX ( CS1, CS1[metric1_subtotal] )
    )
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@hashariI am not sure how do you envision the calculation to pan out on a drill down level

 

On individualsdv level (non-subtotal filter context), I am telling my measure to calculate evrything but that particular sdv level. So with sdv=2, it sums up 4+5+7+10.

When you drill down and gove to sdv->srg lvel what do you plan this measure to return for sdv-srg level with 2-20 and 2-51?

 

Capture.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@smpa01 

For 2-20 would it be possible to display 727 (870 - 143), instead of 640

For 2-51 would it be possible to display 783 (870 - 87), instead of 640

 

Also, would that carry if we drill down to Level-3, 'sds' ?

hashari_0-1614794821229.png

 

@hasharitry this

 

_test = 
IF (
    OR (
        OR ( ISINSCOPE ( SM[srg] ), ISINSCOPE ( SM[sds] ) ),
        ISINSCOPE ( SM[loc] )
    ),
    CALCULATE ( SUM ( CS1[metric1_subtotal] ), ALLSELECTED ( CS1 ) )
        - SUMX ( CS1, CS1[metric1_subtotal] ),
    IF (
        HASONEVALUE ( SM[sdv] ),
        VAR _1 =
            MAX ( SM[sdv] )
        VAR _2 =
            CALCULATE (
                SUMX ( CS1, CS1[metric1_subtotal] ),
                FILTER ( ALLSELECTED ( SM ), SM[sdv] <> _1 )
            )
        RETURN
            _2,
        SUMX ( CS1, CS1[metric1_subtotal] )
    )
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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