Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@Anonymous , Try with a filter like
filter(all(Table[sceniro]),Table[sceniro] in except(all(Table[sceniro]),values(Table[sceniro])))
@Anonymous - Sounds like you need an inverse aggregator: https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/342266#M91
@Anonymous , Try with a filter like
filter(all(Table[sceniro]),Table[sceniro] in except(all(Table[sceniro]),values(Table[sceniro])))
@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])
@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.
@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.
@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..
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |