Operation on matching rows

[Anonymous]

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!

[rohit_singh]

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! 😊

[SpartaBI · Accepted solution]

@Anonymous 

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

Showcase Report – Contoso By SpartaBI

      

[Anonymous]

Thanks a lot!

[SpartaBI]

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