Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Operation on matching rows

Hello everyone,

I have the following data table:

And I need to be able to do the following operation: When the Date, Cat2 and Cat3 columns match, they multiply the Value column by their match column, i.e. in the example case you would do the following:

Finally, the result should be added, obtaining the following:

30+24+30+35 = 119

Does anyone know how to do it without having to create a new table?

Thank you very much!

Anonymous

Measure = 
    SUMX(
        SUMMARIZE(
            'Table',
            'Table'[Date],
            'Table'[Cat2],
            'Table'[Cat3]
        ),
        CALCULATE(
            PRODUCTX(
                    'Table',
                    [Value]
            )
        )
    )

Showcase Report – Contoso By SpartaBI

4 Replies

SpartaBI
Community Champion
4 years ago
Anonymous

Measure = SUMX( SUMMARIZE( 'Table', 'Table'[Date], 'Table'[Cat2], 'Table'[Cat3] ), CALCULATE( PRODUCTX( 'Table', [Value] ) ) )

Showcase Report – Contoso By SpartaBI
- Anonymous
  Not applicable
  4 years ago
  Thanks a lot!
  - SpartaBI
    Community Champion
    4 years ago
    Anonymous my pleasure 🙂
    Hey, check out my showcase report:
    https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
    Give it a thumbs up if you liked it 🙂
rohit_singh
Solution Sage
4 years ago
Hi Anonymous ,

1) Load your data into report view

2) Add a calculated column to fetch previous value

Prev Value =

CALCULATE(
MAX(Categories[Value]),
FILTER(Categories,
Categories[Cat2] = EARLIER(Categories[Cat2]) &&
Categories[Cat3] = EARLIER(Categories[Cat3]) &&
Categories[Value] < EARLIER(Categories[Value])
)
)

3) Add another calculated column to multily value and previous value

Multiplied value = Categories[Prev Value] * Categories[Value]

4) You can now sum the multiplied value column to get the desired result.

Kind regards,
Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊