cancel
Showing results for
Did you mean:
Highlighted Helper II

## Need help to calculate SUM of Cases for Source and Destination

Hello All,

I am trying to calculate sum of cases based on source and destination for each period.

the data looks like as below

 Year Period Platform Scenario Name Source Destination Case 2020 p6 Pretzel Pretzel A Dallas Houston 6 2020 p6 TC TC Dallas Houston 8 2020 P6 NUTS NUTS Dallas Houston 9 2020 P6 Frito Frito Austin Miami 11 2020 P5 PC PC Dallas Houston 7 2020 P5 DIPS DIPS Dallas Houston 10 2020 p5 Pretzel Pretzel A Dallas Houston 11 2020 p6 Pretzel Pretzel A Aberdeen Brooklyn 7

When i choose Pretzel A from Scenario Name column,

the expected output looks like as below

 Year Period Platform Scenario Name Source Destination Case 2020 P6 Pretzel Pretzel A Dallas Houston 6 + (8 + 9) 2020 P6 TC TC Dallas Houston 8 + (6+9) Here...

i need to get sum of all cases grouped by Source - destination & Year-Period combination where

for one scenario name(in this case, Pretzel A) which are having Source and destination similar to other Scenario Names (like Dallas and Houston)

I am having difficulty to put it in words, so you can imagine, that, how i can make it work in powerbi using DAX.

I need some suggestions whether it is possible using DAX, if it is, can anyone please guide.

Thanks,

Mohan V.

11 REPLIES 11
Highlighted Super User IX

## Re: Need help to calculate SUM of Cases for Source and Destination

@MohanV , logic is still not clear to me. Can explain how each line merged

Proud to be a Super User!

Highlighted Resolver III

## Re: Need help to calculate SUM of Cases for Source and Destination

I don't know if I completely understand your question, but I think that this measure does the trick:

``(M) Grouped Sum = CALCULATE(SUM('table'[Case]), ALLEXCEPT('table', 'table'[Year], 'table'[Period], 'table'[Source], 'table'[Destination]))``

Maybe you can try it out, let me know!

Highlighted Helper II

## Re: Need help to calculate SUM of Cases for Source and Destination

The output i am expecting, there i am not merging the lines.

i am just trying to show that, how the rows values should be summed.

 Year Period Platform Scenario Name Source Destination Case 2020 P6 Pretzel Pretzel A Dallas Houston 6 + (8 + 9) = 23 2020 P6 TC TC Dallas Houston 8 + (6+9) =23

Here in first row, 6 is from

 2020 p6 Pretzel Pretzel A Dallas Houston 6

and 8 is from

 2020 p6 TC TC Dallas Houston 8

and 9 is from

 2020 P6 NUTS NUTS Dallas Houston 9

need to show Total sum of Cases for Pretzel A scenario is 23.

here, source,destination, Year-period are same but for 1st Scenario i.e Pretzel A, along with that, i need to consider the other Scenarios which are not Pretzel A but same Source, Destination and Year-Period.

like wise,

 2020 P6 TC TC Dallas Houston 8 +(6+9) = 23

8 =

 2020 p6 TC TC Dallas Houston 8

6 =

 2020 p6 Pretzel Pretzel A Dallas Houston 6

9=

 2020 P6 NUTS NUTS Dallas Houston 9

 2020 p5 PC PC Dallas Houston 7 +(10+11) = 28

7 =

 2020 P5 PC PC Dallas Houston 7

10=

 2020 P5 DIPS DIPS Dallas Houston 10

11=

 2020 p5 Pretzel Pretzel A Dallas Houston 11

Hope you understood what i am trying to achive here.

Thanks,

Mohan V.

Highlighted Resolver III

## Re: Need help to calculate SUM of Cases for Source and Destination

Still not 100% sure how you would like to group . Using the measure i mentioned in my previous reply i get these results:

Can you mention which line is wrong and why? Highlighted Helper II

## Re: Need help to calculate SUM of Cases for Source and Destination

@Jef , @amitchandak  Sorry, i forgot to add another row in the data where the logic becomes different than the current one. This is where i was facing the issue.

Thanks,

Mohan V.

Highlighted Helper II

## Re: Need help to calculate SUM of Cases for Source and Destination

Thanks,

Mohan V.

Highlighted Community Support

## Re: Need help to calculate SUM of Cases for Source and Destination

Hi, @MohanV

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table: You may create a calculated column or a measure as below.

``````Calculated column:
Grouped Sum Column =
var _yearperiod = [Year-Period]
var _source = [Source]
var _destination = [Destination]
return
CALCULATE(
SUM('Table'[Case]),
FILTER(
ALL('Table'),
'Table'[Year-Period]=_yearperiod&&
'Table'[Source]=_source&&
'Table'[Destination]=_destination&&
NOT(CONTAINSSTRINGEXACT('Table'[Scenario Name],"- THIN REG"))
)
)

Measure:
Grouped Sum measure =
var _yearperiod = SELECTEDVALUE('Table'[Year-Period])
var _source = SELECTEDVALUE('Table'[Source])
var _destination = SELECTEDVALUE('Table'[Destination])
return
CALCULATE(
SUM('Table'[Case]),
FILTER(
ALL('Table'),
'Table'[Year-Period]=_yearperiod&&
'Table'[Source]=_source&&
'Table'[Destination]=_destination&&
NOT(CONTAINSSTRINGEXACT('Table'[Scenario Name],"- THIN REG"))
)
)``````

Result: Best Regards

Allan

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

Highlighted Helper II

## Re: Need help to calculate SUM of Cases for Source and Destination

Much appriciated.

I did some modifications based on your code as you gave a hardcoded contained string.

What i did is, i added Index column in table, and based on that i calculated Platform Count using below DAX

``````PlatformCount =
CALCULATE (
COUNT ( MasterTable[Platform] ),
FILTER (
ALLEXCEPT('MasterTable',MasterTable[Year-Period],MasterTable[Source-Dest]),
[Index] <= EARLIER ( 'MasterTable'[Index] )
&& [Platform] = EARLIER ( 'MasterTable'[Platform])
)
)``````

Now instead of CONTAINSTRING function i changed the code to below dax.

``````Grouped Sum Column =
var _yearperiod = [Year-Period]
var _source = [Source]
var _destination = [Destination]
Var _Platform = [Platform]
VAR _Scenario = MasterTable[Scenario Name]
return
CALCULATE(
SUM('MasterTable'[_Cases]),
FILTER(
ALL('MasterTable'),
'MasterTable'[Year-Period]=_yearperiod&&

'MasterTable'[Source]=_source&&
'MasterTable'[Destination]=_destination&&
MasterTable[PlatformCount]=1
//NOT(CONTAINSSTRINGEXACT('MasterTable'[Scenario Name],"- THIN REG"))
)
)``````

Because what ever the solution you gave it works for the sample data that i have provided but the thing here is, the string can be anything going forward.

So i calculated PlatformCount and mentioned the condition as it should be 1.

So that i can neglect all the other same duplicated Platform rows. The problem here is, i am getting same sum value for each Scenario here.

For example, For PC, the sum value is 10 which is corrent but when i choose the Pretzel- THIN REG the sum value should be 12+3 = 15

Because, for choosen Scenario which is Pretzel - THIN REG, we need to consider that row value which is 12 and we have to exclude the other two rows which are same platform i.e Pretzel but same Source and destination

and for PC, based on platform count need to consider the MIN value, so it is 3,

but not PC-Thin Reg.

Thanks,

Mohan V.

For Abredeen Source and Broklyn Destination, PC

Highlighted Helper II

## Re: Need help to calculate SUM of Cases for Source and Destination

Thanks,

Mohan V.

Announcements #### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge! #### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (775)