Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone, I need your help.
I am having trouble with using quick calc and report level filters. In the examples below Cancelled is 10.62% but when I use the report level filters it changes to 47.24%. I only want to see certain status groups in the chart because the other ones are not relevant. Is there any way that I can filter out certain groups and keep the Cancelled at 10.62%? If there is a better way to do this please let me know. The data file is quite large at 54MB with 253,951 detail records and I am not an expert at this at all. Thanks in advance for your help.
Try a measure as
% of GT = IF ( HASONEVALUE ( Cancelled[Status Group] ) && LASTNONBLANK ( Cancelled[Status Group], 0 ) = "Cancelled", SUM ( [Amt] ) / SUMX ( ALL ( Cancelled ), [Amt] ), SUM ( [Amt] ) / SUMX ( ALLSELECTED ( Cancelled ), Cancelled[Amt] ) )
Try something like this on for size:
% of GT = IF(VALUES(Cancelled[Category])="Cancelled",SUM([Value])/SUMX(ALL(Cancelled),[Value]),SUM([Value])/SUMX(ALLEXCEPT(Cancelled,Cancelled[Category]),[Value]))
I used this together with this query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s/IzFfSUXLOzynNTSot1lHw99BRCA12VIrVQUjmpJal5iTmpWCXzcxLzszLSyzJRJX2zEvJTMxLBKqAsgryczKBFnj6QdXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [State = _t, City = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"City", type text}}) in #"Changed Type"
smoupre, I sent you a private message.
Thanks
There are functions like ALL and ALLEXCEPT that can do this. However, since it looks like you want one category "Cancelled" to be one way and everything esle to be another way, this could get a little tricky. Can you post a small subset of your data, doesn't need to be more than about 20 rows or so or at least a sense of what the structure is. Is it just:
Category,Value
?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |