cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bluebird1996
Regular Visitor

Dynamic Denominator using Multiple Filters - I need a Genius

Trying to create a denominator dynamically.  Here is my DAX.  

 

Shipped group total =
CALCULATE ( SUMX ( KS1, KS1[shipped] )ALLEXCEPT ( 'KS1', KS1[Month_SHP] ) )

 

So for each month_SHP and age in months we have a Shipped group total....

This looks fine because the denominator should not change by age of kits. The problem is the column total, which seems to count ALL the kits.. 

bluebird1996_0-1621306196555.png

This is what I want the table to look like.  Coluns 2 and 3 only total those kits that have and an age of 2 and 3 months repectively.

bluebird1996_1-1621306487481.png

 

Seems so simple, but I am SO STUCK.  Please Help!!

 

Here is the .PBIX

 

Thank you

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @bluebird1996 

I am not quite sure whether it suits your case, but please try to change the measure like below.

 

Shipped group total =
SUMX (
VALUES ( KS1[Month_SHP] ),
CALCULATE ( SUMX ( KS1, KS1[shipped] ), ( ALLEXCEPT ( KS1, KS1[Month_SHP] ) ) )
)

 

Picture4.png

 

https://www.dropbox.com/s/jjtod7tilnn1x93/Kit%20test.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

3 REPLIES 3
bluebird1996
Regular Visitor

Hi JiHwan Kim, 

 

Thank you for an elegant solution, you were indeed the genius I needed.  

 

My original data had many other columns of course, and as I sliced and diced the result, I had to add those additional columns to the ALLEXCEPT clause.  I wonder is there a way to design this so addional columns would not have to be added to the clause?  

 

In any event, I thank you for your kind assistance and profound expertise!!!

Best to you. 🙂 

 

Hi, @bluebird1996 

Thank you for your feedback.

It is quite difficult to tell without seeing the full picture how the data model looks like.

If it is ok with you, please share how your table looks like, and how other columns are included, then I can try to look into it to come up with a more accurate measure.

Thank you. 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Jihwan_Kim
Community Champion
Community Champion

Hi, @bluebird1996 

I am not quite sure whether it suits your case, but please try to change the measure like below.

 

Shipped group total =
SUMX (
VALUES ( KS1[Month_SHP] ),
CALCULATE ( SUMX ( KS1, KS1[shipped] ), ( ALLEXCEPT ( KS1, KS1[Month_SHP] ) ) )
)

 

Picture4.png

 

https://www.dropbox.com/s/jjtod7tilnn1x93/Kit%20test.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors