cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

Hi!

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

I have a table with 3 columns:

danzlo94_0-1659971372067.png

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!

1 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

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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.

@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

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Anonymous
Not applicable

Worked like a charm! Thank you @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
Community Champion
Community Champion

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors