Forum Discussion

YDi's avatar
YDi
New Member
2 years ago
Solved

Total and subtotal are getting calculated wrong for one column

Hi All, 

I am just using the total and subtotal functionality in the matrix and strangley the subtotal is coming good in 1 column and for the other column I am not getting the right value - it's just picking up the bigger number out of 2. 

Major Value should be Minor 1 + Minor 2 as coming up in Column 1. 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi YDi ,

    Your solution is great, Greg_Deckler . I have another idea here, and I wanted to share it for reference.


    Incorrect measure in totals is a relatively common scenario where the measure follows the context of the ‘total’ row and is calculated in that context. As a result, a measure used in a column of a table visualisation may have an unexpected value in the Total column.


    You can use the following measures, use the IF() + ISINSCOPE() functions to determine the data level, and then perform different calculations depending on the level.

     

    I created these data:

     

    1. create the MEASURE and place it directly in the matrix, which will appear as you describe.

    test =
MAX('Table'[value])

     

    2. create MEASURE, modify it using the ISINSCOPE function.

    MEASURE =
VAR _sum =
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ), FILTER (
'Table'[item] = MAX ( 'Table'[item] )
&& 'Table'[code] = MAX ( 'Table'[code] )
)
)
RETURN
IF (
ISINSCOPE ( 'Table'[subitem] ),
'Table'[test],
IF ( ISINSCOPE ( 'Table'[item] ), _sum, SUMX ( 'Table', 'Table'[test] ) )
)

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies