I am trying to calculate sum of cases based on source and destination for each period.
the data looks like as below
When i choose Pretzel A from Scenario Name column,
the expected output looks like as below
|2020||P6||Pretzel||Pretzel A||Dallas||Houston||6 + (8 + 9)|
|2020||P6||TC||TC||Dallas||Houston||8 + (6+9)|
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.
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!
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.
|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
and 8 is from
and 9 is from
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.
|2020||P6||TC||TC||Dallas||Houston||8 +(6+9) = 23|
|2020||p5||PC||PC||Dallas||Houston||7 +(10+11) = 28|
Hope you understood what i am trying to achive here.
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?
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
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")) ) )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-alq-msft Thanks for your help.
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.
For Abredeen Source and Broklyn Destination, PC
Check out a full recap of the month!
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.