Dynamic Denominator using Multiple Filters - I need a Genius

[Anonymous]

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

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.

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

Here is the .PBIX

Thank you

[Anonymous]

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

[Jihwan_Kim]

Hi, @Anonymous 

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. 

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

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

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