Summarise rows in Power Bi - Tableau to Power Bi Migration

[Anonymous]

Hi Experts,

I am need to achieve below:

1. Calculate the sum of impact over Event Start Date, Headline, Location and Class columns.
2. The record having highest event no. should be visible, if event no. are equal, then the record having highest impact code should be visible.

My Impact column shows correct values till I select both the event types from slicer but getting incorrect impact when select any one event type.

See below, on selecting Unplanned Shutdown, My Impact should be 1.92 but it is coming as 2.09

On selecting Unplanned slowdown, it should show 0.17 as below, but it is coming blank.

I didn't see an option to share the pbix. Hence, below is the code for My Impact measure I have created.

+++++++++++++++++++++++

My Impact =

SUMX (

VAR _partition =

ALLEXCEPT (

'Table',

'Table'[event start date],

'Table'[Class],

'Table'[Location],

'Table'[headline]

)

VAR _2 =

ADDCOLUMNS (

ADDCOLUMNS (

'Table',

"@filt", CALCULATE ( MAX ( 'Table'[Event No.] ), _partition )

),

"Total",

CALCULATE (

sum([Impact]),

_partition

)

)

VAR _3 =

SUMMARIZE (

FILTER ( _2, 'Table'[Event No.] = [@filt] ),

[event start date],

[Event Type],

[Event No.],

[impact code],

[Impact No.],

[Location],

[headline],

[Class],

[priority unit],

[Total],

[@filt]

)

VAR _4 =

ADDCOLUMNS (

_3,

"@filt1", CALCULATE ( MAX ( 'Table'[Impact No.] ), _partition )

)

VAR _5 =

SUMMARIZE (

FILTER ( _4, 'Table'[Impact No.] = [@filt1] ),

[event start date],

[Event Type],

[Event No.],

[impact code],

[Impact No.],

[Location],

[headline],

[Class],

[priority unit],

[Total],

[@filt1]

)

RETURN

_5,

[Total]

)

+++++++++++++++++++++++

[v-angzheng-msft]

Hi, @Anonymous 

Try to create a measure like this:

_MY Impact = 
VAR _max_EventNO=CALCULATE(MAX('Table'[Event No.]),ALLEXCEPT('Table','Table'[Event Start Data],'Table'[Class],'Table'[Location],'Table'[Headline],'Table'[Event Type]))
VAR _max_ImpactNO=CALCULATE(MAX('Table'[Impact  No]),ALLEXCEPT('Table','Table'[Event Start Data],'Table'[Class],'Table'[Location],'Table'[Headline],'Table'[Event Type]),'Table'[Event No.]=_max_EventNO)
VAR _IsVisible=IF(MAX('Table'[Event No.])=_max_EventNO&&MAX('Table'[Impact  No])=_max_ImpactNO,1)
VAR _SumImpact=CALCULATE(SUM('Table'[Impact]),ALLEXCEPT('Table','Table'[Event Start Data],'Table'[Class],'Table'[Location],'Table'[Headline],'Table'[Event Type]))

return IF(_IsVisible=1,_SumImpact)

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Anonymous]

Thanks @v-angzheng-msft for looking into this! The solution you shared works fine when I select single Event Type but shows incorrect values when both values are selected. Below is the expected output when both event types are selected.

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try this:

_MY Impact = 
VAR _max_EventNO=CALCULATE(MAX('Table'[Event No.]),ALLEXCEPT('Table','Table'[Event Start Data],'Table'[Class],'Table'[Location],'Table'[Headline],'Table'[Event Type]))
VAR _max_ImpactNO=CALCULATE(MAX('Table'[Impact  No]),ALLEXCEPT('Table','Table'[Event Start Data],'Table'[Class],'Table'[Location],'Table'[Headline],'Table'[Event Type]),'Table'[Event No.]=_max_EventNO)
VAR _IsVisible=IF(MAX('Table'[Event No.])=_max_EventNO&&MAX('Table'[Impact  No])=_max_ImpactNO,1)
VAR _SumImpact=CALCULATE(SUM('Table'[Impact]),ALLEXCEPT('Table','Table'[Event Start Data],'Table'[Class],'Table'[Location],'Table'[Headline],'Table'[Event Type]))
VAR _IF=IF(_IsVisible=1,_SumImpact)

// Multi-select
VAR _max_EventNO2=CALCULATE(MAX('Table'[Event No.]),ALLEXCEPT('Table','Table'[Event Start Data],'Table'[Class],'Table'[Location],'Table'[Headline]))
VAR _max_ImpactNO2=CALCULATE(MAX('Table'[Impact  No]),ALLEXCEPT('Table','Table'[Event Start Data],'Table'[Class],'Table'[Location],'Table'[Headline]),'Table'[Event No.]=_max_EventNO)
VAR _IsVisible2=IF(MAX('Table'[Event No.])=_max_EventNO2&&MAX('Table'[Impact  No])=_max_ImpactNO2,1)
VAR _SumImpact2=CALCULATE(SUM('Table'[Impact]),ALLEXCEPT('Table','Table'[Event Start Data],'Table'[Class],'Table'[Location],'Table'[Headline]))
VAR _IF2=IF(_IsVisible2=1,_SumImpact2)


VAR _SLICER=IF(CALCULATE(DISTINCTCOUNTNOBLANK('Table'[Event Type]),ALLSELECTED('Table'))<=1,1,2)
VAR _RESULT=IF(_SLICER=1,_IF,_IF2)

return _RESULT

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Anonymous]

It works as expected! Thanks heaps for your help 🙂

[lbendlin]

Your visuals aren't grouped at the level that you expect. Do you want the results to be auto calculated in the visual or do you want to use measures to calculate the value across groups regardless of the visual fields?

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.