How to group and sum row values over other columns in Power BI desktop

[Anonymous]

Hi Experts,

I am new to Power BI dax and trying to achieve below but couldn't succeed. Could you please help?

Problem: I want to group the rows by Start_date, Location, headline, Unit and sum the first_impact and second impact. While summing the rows, i want to display row having higher Plan Code.

Note: First_Impact and Second_Impact has been calcuated in separate measure using user input slicer filters.

Start_date	Plan	Plan Code	Location	headline	Unit	Priority	First_Impact	Second_Impact	Total Impact
1/1/2021	Dom	15	Site_1	Air limit	D_Other GDC	FALSE	0.00	0.47	0.47
1/1/2021	Test Planned	8	Site_1	This is heater	My_Poker	FALSE	0.01	0.00	0.01
1/1/2021	Planned	10	Site_1	This is heater	My_Poker	TRUE	0.06	0.00	0.06
1/1/2021	Planned	10	Site_2	safety regulation	Ge_Cycle	FALSE	0.04	0.00	0.04
1/1/2021	Unplanned	5	Site_2	limitations train	Ge_B12	TRUE	0.02	0.00	0.02

Below is what i want to achieve:

Start_date	Plan	Plan Code	Location	headline	Unit	Priority	First_Impact	Second_Impact	Total Impact
1/1/2021	Dom	15	Site_1	Air limit	D_Other GDC	FALSE	0.00	0.47	0.47
1/1/2021	Planned	10	Site_1	This is heater	My_Poker	TRUE	0.07	0.00	0.07
1/1/2021	Planned	10	Site_2	safety regulation	Ge_Cycle	FALSE	0.04	0.00	0.04
1/1/2021	Unplanned	5	Site_2	limitations train	Ge_B12	TRUE	0.02	0.00	0.02

[v-chenwuz-msft]

Hi @Anonymous ,

Try these measures to replace first, sencond and total impact.

Measure_First_impact =
VAR _group =
    FILTER(
        ALL( 'Table' ),
        [Start_date] = MAX( 'Table'[Start_date] )
            && [Location] = SELECTEDVALUE( 'Table'[Location] )
            && [Unit] = SELECTEDVALUE( 'Table'[Unit] )
    )
VAR _sum =
    SUMX( _group, [First_Impact] )
VAR _maxcode =
    CALCULATE( MAX( 'Table'[Plan Code] ), _group )
RETURN
    IF( MAX( 'Table'[Plan Code] ) = _maxcode, _sum, BLANK() )

Measure_Second_impact =
VAR _group =
    FILTER(
        ALL( 'Table' ),
        [Start_date] = MAX( 'Table'[Start_date] )
            && [Location] = SELECTEDVALUE( 'Table'[Location] )
            && [Unit] = SELECTEDVALUE( 'Table'[Unit] )
    )
VAR _sum =
    SUMX( _group, [Second_Impact] )
VAR _maxcode =
    CALCULATE( MAX( 'Table'[Plan Code] ), _group )
RETURN
    IF( MAX( 'Table'[Plan Code] ) = _maxcode, _sum, BLANK() )

Measure_Total_impact = [Measure_First_impact]+[Measure_Second_impact]

result:

I put my pbix file in the end and you can reference. If I misunderstood you please let me know.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-chenwuz-msft : Many thanks for your inputs! The solution works only if we have only single start_date. I have tried your solution with below data and it didn't gave any values for first and second impact. Pls correct me, if I am missing something here.

Start_date	Plan	Plan Code	Location	headline	Unit	Priority	First_impact	Second_impact	Total Impact
1/1/2021	Dom	15	Site_1	Air limit	D_Other GDC	FALSE	0	0.47	0.47
1/1/2021	Planned	10	Site_1	This is heater	My_Poker	TRUE	0.06	0	0.06
1/1/2021	Test Planned	8	Site_1	This is heater	My_Poker	FALSE	0.01	0	0.01
1/1/2021	Unplanned	5	Site_2	limitations train	Ge_B12	TRUE	0.02	0	0.02
1/1/2021	Planned	10	Site_2	safety regulation	Ge_Cycle	FALSE	0.04	0	0.04
1/3/2021	Unplanned	5	Site_1	limitations train	Ge_B12	FALSE	0.02	0.01	0.03
1/3/2021	Planned	10	Site_1	limitations train	Ge_B12	TRUE	0.03	0.04	0.07
1/3/2021	Unplanned	5	Site_1	limitations train	Ge_B12	FALSE	0.01	0.01	0.02
2/4/2021	Planned	10	Site_2	safety regulation	My_Poker	FALSE	2	3	5
2/4/2021	Dom	15	Site_2	safety regulation	My_Poker	TRUE	5	1	6

Expected output:

Start_date	Plan	Plan Code	Location	headline	Unit	Priority	First_impact	Second_impact	Total Impact
1/1/2021	Dom	15	Site_1	Air limit	D_Other GDC	FALSE	0	0.47	0.47
1/1/2021	Planned	10	Site_1	This is heater	My_Poker	TRUE	0.07	0	0.07
1/1/2021	Planned	10	Site_2	safety regulation	Ge_Cycle	FALSE	0.04	0	0.04
1/3/2021	Unplanned	10	Site_1	limitations train	Ge_B12	TRUE	0.08	0.06	0.14
2/4/2021	Dom	15	Site_2	safety regulation	My_Poker	TRUE	7	4	11

[v-chenwuz-msft]

Hi @Anonymous ,Can you explain why 1/3/2021 first_impact is 0.08, it is added to the 0.02 in the fourth line to get it? If so, your grouping should be based on location,headline, unit. without the start_date . However, the location in the fourth line is Site_2, which is not the same as the location of 1/3/2021. Please let me know if I have misunderstood.

[Anonymous]

@v-chenwuz-msft: My sincere apologies, there is a correction in the expected output (see corrected output below).

Please note, the requirement is:

1. First_Impact, Second_Impact and Total Impact are getting calculated over -> Start_date, Location, headline, Unit )). Then, displaying the row where Plan Code is maximum.

Start_date	Plan	Plan Code	Location	headline	Unit	Priority	First_impact	Second_impact	Total Impact
01-01-2021	Dom	15	Site_1	Air limit	D_Other GDC	FALSE	0	0.47	0.47
01-01-2021	Planned	10	Site_1	This is heater	My_Poker	TRUE	0.07	0	0.07
01-01-2021	Unplanned	5	Site_2	limitations train	Ge_B12	TRUE	0.02	0	0.02
01-01-2021	Planned	10	Site_2	safety regulation	Ge_Cycle	FALSE	0.04	0	0.04
01-03-2021	Planned	10	Site_1	limitations train	Ge_B12	TRUE	0.06	0.06	0.12
02-04-2021	Dom	15	Site_2	safety regulation	My_Poker	TRUE	7	4	11

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous 

try this :

Measure_First_impact =
VAR _group =
    FILTER(
        ALL( 'Table' ),
        // [Start_date] = MAX( 'Table'[Start_date] )
        // && 
        [Location]
            = SELECTEDVALUE( 'Table'[Location] )
            && [Unit] = SELECTEDVALUE( 'Table'[Unit] )
            && [headline] = SELECTEDVALUE( 'Table'[headline] )
    )
VAR _sum =
    SUMX( _group, [First_Impact] )
VAR _maxcode =
    CALCULATE( MAX( 'Table'[Plan Code] ), _group )
RETURN
    IF( MAX( 'Table'[Plan Code] ) = _maxcode, _sum, BLANK() )

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-chenwuz-msft It worked!! Thanks you so much 🙂

[Fowmy]

@Anonymous 

Following table shoudl give yo the expected results:

FILTER (
    ADDCOLUMNS (
        Table1,
        "@First Impact", [First Impact],
        "@Second Impact", [Second Impact],
        "@Total Impact", [Total Impact]
    ),
    VAR _PlanCode =
        CALCULATE (
            MAX ( Table1[Plan Code] ),
            ALLEXCEPT (
                Table1,
                Table1[Start_date],
                Table1[Location],
                Table1[headline],
                Table1[Unit]
            )
        )
    RETURN
        Table1[Plan Code] = _PlanCode
)

[Anonymous]

@Fowmy Thanks for your reply. The output I am getting is little different from expected. I want to sum up the values of first impact and second impact rows over start_date, location, headline and unit. Hence, the value should be 0.07 (0.06+0.01) in the highlighted row.