Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Thank you
Solved! Go to Solution.
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] ) ) )
)
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 accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 accepting it as the solution to help other members find it faster, and give a big thumbs up.
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] ) ) )
)
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 accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |