Forum Discussion

SriRed123's avatar
SriRed123
Regular Visitor
6 years ago

Help with aggregation in matrix for different total value

Hi, I have table with below data, which I am trying to aggregate into a matrix Store  Product  Day  Interim  Final Store1  Prod1 Day1 10 20 Store1  Prod1 ...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi SriRed123 ,

 

You can try this measure

 

Quantity =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[ Day] ), SUMX (
        'Table',
        IF (
            'Table'[ Interim] > 'Table'[ Final],
            'Table'[ Interim],
            'Table'[ Final]
        )
    ),
    SUMX (
        SUMMARIZE (
            'Table',
            'Table'[Store],
            'Table'[ Day],
            'Table'[ Interim],
            'Table'[ Final]
        ),
        IF (
            MAX ( 'Table'[ Final] )
                >= MAX ( 'Table'[ Interim] ),
            'Table'[ Final],
            'Table'[ Interim]
        )
    )
)

 

 

 

 

 


Regards,

Harsh Nathani


Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!