Need Help in calculating Historical cases

[Anonymous]

Hello All,

i am looking for a helping hand who can suggest me or help me to calculate sum of historical cases.

The Data looks as below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZI/C8IwEMW/imTucPlXXRMrOgYcSweRDl1a0Pr97V3BoTmawyGPBPLjvbxc2yoDBqBWlQq4UO7T5/XsD3rZNv17HsbHPEwjnbWBRa0H1VVbNEpQz6JnCXraomgYRYE1MGgpsLEOlUNjMbDz2BPLNhJbx6KXfdRZbPi/t+LNmjW97qOeXI8seiu4Av1rhibBTOAMcjUlksJzVzhvaoVFzllXgYYipAIMv9jdFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Platform = _t, #"Scenarion Name" = _t, #"Source Name" = _t, #"Destination Name" = _t, #"Historical Cases" = _t, #"Scenario Cases" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}, {"Platform", type text}, {"Scenarion Name", type text}, {"Source Name", type text}, {"Destination Name", type text}, {"Historical Cases", Int64.Type}, {"Scenario Cases", Int64.Type}})
in
    #"Changed Type"

When i select scenario name as AC,

the calculation should be like.

SUM OF SCENARIO CASES (850+400) where Scenario cases = AC for selected Source, destination and Period.

Now, for calculating Historical cases sum, 

i need to exclude the Platforms for which the scenario name was selected.

Means,

Above i have selected scenario name as AC, and it has 2 platforms A and BP.

So, i have to exclude A and BP Platforms, and calculate the sum of historical cases for other platforms (120+234+1245+234+43+236+543+200+300).

I got calculated Scenario cases using below dax.

can anyone please help me to calculate the historical cases.

Thanks in advance,

Mohan V.

[Greg_Deckler]

@Anonymous - Sounds like you need an inverse aggregator: https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/342266#M91

[amitchandak · Accepted solution]

@Anonymous , Try with a filter like

filter(all(Table[sceniro]),Table[sceniro] in except(all(Table[sceniro]),values(Table[sceniro])))

[Anonymous]

@amitchandak , @Greg_Deckler  can we convert this measure into calculated column.

Please help.

Thanks,

Mohan V.

[amitchandak]

@Anonymous , try like 

sumx(filter(all(Table[sceniro]),Table[sceniro] <> earlier(Table[sceniro])),[sceniro class])

[Anonymous]

@amitchandak @v-diye-msft @Greg_Deckler 

I tried all your suggestions and i even tried some diiferent ways but non of them are working fine.

i am still unable to figure it out how to get it solved.

I sincearly need your help.

I have to show that InverseSum values in table visual by including Platform, Source, Dest, and period as above image.

Now when i dont have Source, Dest, Platform in my table visual and i keep only Period and InverseSum i am getting the right values.

I am seriously struck here.
the totla value of historycases for NON A, BP platform is 3155.

it should be same even though i keep other columns like source, dest, platform, scenario, because it should exclude these filtereing criteria.

Please help.

Thanks,

Mohan V.

[Greg_Deckler]

@Anonymous - Can you post your data as text as well as what you want as output? 
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

@amitchandak, @Greg_Deckler  Thanks for the reply means a lot.

I tried your solution, by doing some changes, 

_HistoricalCases = CALCULATE(SUM('Table'[Historical Cases]), filter(all('Table'),'Table'[Platform] in except(all('Table'[Platform]),values('Table'[Platform])))
)

I got the below output.

 The total value which i see here 3155 is what i am expecting in both the rows..

[v-diye-msft]

Hi @Anonymous 

If you've fixed it by your own, please kindly mark it as a answer that help others find it more quickly. thanks!

If  not, please kindly elaborate more.