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
michaelwy
Frequent Visitor

% Of GT

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.

 

Example 1.JPGExample 2.JPG

 

 

 

4 REPLIES 4
Eric_Zhang
Employee
Employee

@michaelwy

 

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] )
)

 

Capture.PNG

Greg_Deckler
Super User
Super User

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"

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

smoupre, I sent you a private message.

 

Thanks

Greg_Deckler
Super User
Super User

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

 

 

?


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

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.