Need help to calculate SUM of Cases for Source and Destination

[Anonymous]

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.

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft]

Hi, @Anonymous 

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.

[Anonymous]

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

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

[Anonymous]

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

Thanks,

Mohan V.

[v-alq-msft]

Hi, @Anonymous 

The measure will reflect the selection of slicer, while the column will change only when the data is refreshed and loaded, and cannot interact with other visuals.

You may try to create the following two measures.

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

Grouped Sum Column =
VAR _yearperiod =
    SELECTEDVALUE ( 'MasterTable'[Year-Period] )
VAR _source =
    SELECTEDVALUE ( 'MasterTable'[Source] )
VAR _destination =
    SELECTEDVALUE ( 'MasterTable'[Destination] )
VAR _Platform =
    SELECTEDVALUE ( 'MasterTable'[Platform] )
VAR _Scenario =
    SELECTEDVALUE ( MasterTable[Scenario Name] )
RETURN
    CALCULATE (
        SUM ( 'MasterTable'[_Cases] ),
        FILTER (
            ALL ( 'MasterTable' ),
            'MasterTable'[Year-Period] = _yearperiod
                && 'MasterTable'[Source] = _source
                && 'MasterTable'[Destination] = _destination
                && MasterTable[PlatformCount] = 1
        )
    )

Best Regards

Allan

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

[Anonymous]

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!

[amitchandak]

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

[Anonymous]

@amitchandak, @Anonymous  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.

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.

[Anonymous]

@Anonymous 

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? 

[Anonymous]

@Anonymous , @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, 

This is where i was facing the issue.

Thanks,

Mohan V.

[Anonymous]

@Anonymous @amitchandak Can you please help.

Thanks,

Mohan V.