Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.