cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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

 

YearPeriodPlatformScenario NameSourceDestinationCase
2020p6PretzelPretzel ADallasHouston6
2020p6TCTCDallasHouston8
2020P6NUTSNUTSDallasHouston9
2020P6FritoFritoAustinMiami11
2020P5PCPCDallasHouston7
2020P5DIPSDIPSDallasHouston10
2020p5PretzelPretzel ADallasHouston11
2020p6PretzelPretzel AAberdeenBrooklyn7


When i choose Pretzel A from Scenario Name column,

the expected output looks like as below

 

YearPeriodPlatformScenario NameSourceDestinationCase
2020P6PretzelPretzel ADallasHouston6 + (8 + 9)
2020P6TCTCDallasHouston8 + (6+9)

 

Capture.PNG

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
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Resolver III
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
Helper II

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

@amitchandak@Jef  thanks for the reply.

 

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.

 

YearPeriodPlatformScenario NameSourceDestinationCase
2020P6PretzelPretzel ADallasHouston6 + (8 + 9) = 23
2020P6TCTCDallasHouston8 + (6+9) =23

 

Here in first row, 6 is from 

2020p6PretzelPretzel ADallasHouston6

and 8 is from 

2020p6TCTCDallasHouston8

and 9 is from 

2020P6NUTSNUTSDallasHouston9

 

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, 

2020P6TCTCDallasHouston8 +(6+9) = 23

8 = 

2020p6TCTCDallasHouston8

6 = 

2020p6PretzelPretzel ADallasHouston6

9=

2020P6NUTSNUTSDallasHouston9

 

2020p5PCPCDallasHouston7 +(10+11) = 28

7 = 

2020P5PCPCDallasHouston

7

10=

2020P5DIPSDIPSDallasHouston10

11=

2020p5PretzelPretzel ADallasHouston11

 

Hope you understood what i am trying to achive here.

Thanks,

Mohan V.

Highlighted
Resolver III
Resolver III

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

 

@MohanV 

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? 

Jef_0-1595492891491.png

 

Highlighted
Helper II
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.

Please follow the below image, 

Capture.PNG

This is where i was facing the issue.

Thanks,

Mohan V.

Highlighted
Helper II
Helper II

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

@Jef @amitchandak Can you please help.

Thanks,

Mohan V.

Highlighted
Community Support
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:

a1.png

 

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:

a2.png

 

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
Helper II

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

@v-alq-msft  Thanks for your help.

 

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.

Capture1.PNG

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.

 

Any suggestion on this please.

 

Thanks,

Mohan V.

For Abredeen Source and Broklyn Destination, PC

 

Highlighted
Helper II
Helper II

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

@v-alq-msft @amitchandak @Jef  please help.

 

Thanks,

Mohan V.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

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