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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Justas4478
Post Prodigy
Post Prodigy

% of GT while GT ignoring some filters

Hello, I am trying to create dax to calculate % of GT.
I tried the GT option in visualization but it only GT thats in the table.
While I want to filter it agains GT that ignores some filters that are applied to the table.
This is what I get when I try to use GT.

Justas4478_0-1721206468744.png

The total is 2782, but the actual total is 16517.
The 2782 is ~16% of 16517.
Thats what I would want to see instead of 100%.
The table has Top N filter applied to sku number of the product that exist in different table than shorted data.

Justas4478_1-1721206598867.png

I would need to creat measure where GT ignotes top N filter but not other filters.
This is the measure of Shoretd qty dax.

Shorted Qty = 'Outbound Delivery'[Total Demand] - 'Outbound Delivery'[Total Actual]
As you see it is just simple measure that gives me numbers of how much was not fulfiled.
1 ACCEPTED SOLUTION
Justas4478
Post Prodigy
Post Prodigy

I manage to work out the solution.
There might be easier solution but this is what worked for me.


1. I had to create sum measure for base values that ignore sku filter.

Total Actual all sum = CALCULATE(SUM('Outbound Delivery'[Actual]),ALL('Product'[SKU Number]))
Total Demand all sum = CALCULATE(SUM('Outbound Delivery'[Demand]),ALL('Product'[SKU Number]))

2. Then I had to create shorted qty for those measures.
Shorted Qty as total = 'Outbound Delivery'[Total Demand all sum] - 'Outbound Delivery'[Total Actual all sum]

3. I had to divide unfiltered 'Shorted Qty' with filterd one.
Shorted Qty % of total = DIVIDE('Outbound Delivery'[Shorted Qty],'Outbound Delivery'[Shorted Qty as total])

That gave me the result I was looking for.
Justas4478_0-1721209430250.png

View solution in original post

3 REPLIES 3
Justas4478
Post Prodigy
Post Prodigy

I manage to work out the solution.
There might be easier solution but this is what worked for me.


1. I had to create sum measure for base values that ignore sku filter.

Total Actual all sum = CALCULATE(SUM('Outbound Delivery'[Actual]),ALL('Product'[SKU Number]))
Total Demand all sum = CALCULATE(SUM('Outbound Delivery'[Demand]),ALL('Product'[SKU Number]))

2. Then I had to create shorted qty for those measures.
Shorted Qty as total = 'Outbound Delivery'[Total Demand all sum] - 'Outbound Delivery'[Total Actual all sum]

3. I had to divide unfiltered 'Shorted Qty' with filterd one.
Shorted Qty % of total = DIVIDE('Outbound Delivery'[Shorted Qty],'Outbound Delivery'[Shorted Qty as total])

That gave me the result I was looking for.
Justas4478_0-1721209430250.png
bhanu_gautam
Super User
Super User

@Justas4478 ,  First create a mesaure for Grand Total ignoring TOPN Filter

 

GT_Ignoring_TopN = CALCULATE(
SUM('Outbound Delivery'[Total Demand]),
ALL('Outbound Delivery') -- This removes all filters from the 'Outbound Delivery' table
)

 

And one more for percentage

Percentage_of_GT = DIVIDE(
[Shorted_Qty],
[GT_Ignoring_TopN],
0 -- This is the alternate result if the denominator is zero
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






@bhanu_gautam Your solution does not work.
I should have explained that Shorted Qty is result of demand minus actual.
In your solution you divided Shorted Qty by deman, which is not necessery.
In theory what needs to happend shorted qty needs to be divided with Shorted qty that ignores top n filter.
I should have tried to explain better in initial explanation.
You can see my solution that I posted. It should allow to understand how it works.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.