SUM a column with filter on other columns to get a total column

[Anonymous]

Hi!

Been searching a bit now with no success regarding this matter.

I have a table with 3 columns:

I want to sum the Cover Time, filtered on productname and with max inventory date to get the total result showed above on all rows connected to that specific productnumber. If it is another productnumber i want the "Total Cover Time" to be calculated the same.

Any guidance would be much obliged!

Thank you!

[Anonymous]

Hi @SpartaBI and thank you for your solution. Unfortunately i get the same result for every single row. Despite that i have different productnumbers with different cover time.

[SpartaBI · Accepted solution]

@Anonymous had a typo, this is the code:

Total Cover Time = 
VAR _product = 'Table'[ProductNumber]
VAR _max_date = 
    MAXX(
        FILTER(
            'Table',
            'Table'[ProductNumber] = _product 
        ),
        'Table'[InventoryDate]
    )
VAR _result = 
    SUMX(
        FILTER( 
            'Table',
            'Table'[ProductNumber] = _product
                && 'Table'[InventoryDate] = _max_date
        ),
        'Table'[CoverTime]
    )
RETURN
    _result

SUM a column with filter on other columns to get a total column 2022-08-08.pbix

Showcase Report – Contoso By SpartaBI



      

[Anonymous]

Worked like a charm! Thank you @SpartaBI!

[SpartaBI]

@Anonymous my pleasure 🙂
Please don't forget to accept the previous message as a solution for community visibility.
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

[SpartaBI]

@Anonymous 
Here is a link to download a sample solution file:
SUM a column with filter on other columns to get a total column 2022-08-08.pbix

Showcase Report – Contoso By SpartaBI