Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need Help in calculating Historical cases

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

1.PNG

 

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.

Capture.PNG

 

can anyone please help me to calculate the historical cases.

 

Thanks in advance,

Mohan V.

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Try with a filter like

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

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@Anonymous , Try with a filter like

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

Anonymous
Not applicable

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

 

Please help.

 

Thanks,

Mohan V.

@Anonymous , try like 

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

Anonymous
Not applicable

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

2Image.PNG

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.

3Image.PNG

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.

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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

 

 

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.