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.
Hi there,
I am new to DAX and am really struggling to work out how to make a measure work that looks for a particular combination of products in a column and then assigns a contribution weighting based on that combination.
The DAX query I have developed thus far looks like the below but I am unsure where I am going wrong:
measure = IF(LOOKUPVALUE(product[product_name],product[product_name]," Product A")=" Product A",
SUMX(product,
SWITCH(true(),
product[product_name] = " Product B", products[total_sell]*1+SWITCH(true(),product[product_name] = " Product A", products[total_sell]*.30),
product[product_name] = " Product A", products[total_sell]*.30 ,[total_sell])),[total_sell])
My issue occurs on the Product B line. What I would like to accomplish is when a particular deal has Product A, then add 70% of the total sell price of Product A to Product B's total sell
The actual result from my current DAX query looks something like this:
What I need the data to look like is something more like the below, where the 70% from Product A is added to Product B's final total.
I am unsure what to do from here, any help would be really awesome
Cheers,
JJW
Solved! Go to Solution.
try these two measures and tweak as it works for you.
70% of Product A = VAR s = CALCULATE( SUM( Sheet1[Total Sell] ), Sheet1[Product Name] = "Product A" ) RETURN s * 0.70
Add to product B
Product B with 70 % of Product A = SUM( Sheet1[Total Sell] ) +
IF( MAX( Sheet1[Product Name] ) = "Product B", [70% of Product A], 0 )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Can you provide some sample data for solution?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
hi @parry2k
Here is a link to a sample file
https://www.dropbox.com/s/pvb773uvxry1a1i/sample%20data.pbix?dl=0
Cheers!
@jwhibley so you are only interested in product a and b, in your model you have way more products.
Please clarify.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k yes - I am only interested in Product A and Product B from the calculation perspective
try these two measures and tweak as it works for you.
70% of Product A = VAR s = CALCULATE( SUM( Sheet1[Total Sell] ), Sheet1[Product Name] = "Product A" ) RETURN s * 0.70
Add to product B
Product B with 70 % of Product A = SUM( Sheet1[Total Sell] ) +
IF( MAX( Sheet1[Product Name] ) = "Product B", [70% of Product A], 0 )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Glad to hear 🙂
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
also does that 30% + 70% contrubution is fixed.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
The 30% / 70% contribution is always fixed when there is a Product A, if that makes sense
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |