Forum Discussion
Resolver IIMeasure to remove one filter but add another
I have a PBI with multiple data tables. I am trying to do a simple basically sumif but can't seem to make it work.
I have 10 plants that make different products.
3 of those plants all make product ABC.
I have a slicer that lets you pick product ABC from a materal table.
Once that is selected, it tells you how much total volume the entire company (all 10 plants) made of product ABC.
That comes from the volume table and is simply = sum ('volume table'[actual])
What I am stuck on, is how do I make a second measure that says what the total volume for those 3 plants only are?
I have CALCULATE (SUM('volume table'[actual]), ALL('material map'[brand]))
This gives me the total volume for all 10 plants but doesn't filter it to just the 3 that have volume of product ABC.
What do I need to add so that it sums the volume for all brands but only if the actual volume for the selected brand is greather than 0?
Hi Rena
I hope you're doing well!
I created some dummy data based on my understanding of your post. But please let me know if the below is what you are after. Just note that where no selection has been made in the slicer, I have just added "Choose From Slicer" to alert the user to select an option from the slicer. Otherwise, this can be adjusted to whatever you'd like:
Once a user selects from the slicer, the results change accordingly. Again, I've just added dummy data on the basis of what I understood. Please let me know if I've misunderstood.
TotalVolumeForSelectedPlants = VAR _SelectedBrand = SELECTEDVALUE ( 'Material Table'[Brand] ) VAR _PlantsWithSelectedBrand = CALCULATETABLE ( VALUES ( 'Volume Table'[Plant] ) , 'Volume Table'[Brand] = _SelectedBrand , 'Volume Table'[Actual] > 0 ) VAR _result = CALCULATE ( [Sum of Actual Volume] , 'Volume Table'[Plant] IN _PlantsWithSelectedBrand, ALL ( 'Material Table'[Brand] ) ) RETURN IF ( ISBLANK ( _result ) , "Choose From Slicer" , _result )Hope this helps!
Theo 🙂
Got this figured out - leaving it here in case someone else needs it in the future
Brand Volume Test =VAR SelectedBrand = SELECTEDVALUE('Material Map'[Brand])VAR TotalBrands =TREATAS(VALUES('Material Map'[Brand]),'Volume Lock'[Brand])VAR PlantwithBrands =CALCULATETABLE(VALUES('Volume Lock'[Plant Number]),'Volume Lock'[Brand] IN TotalBrands,'Volume Lock'[Volume]>0)RETURNCALCULATE(SUM('Volume Lock'[Volume]),'Volume Lock'[Plant Number] IN PlantwithBrands,ALL('Material Map'[Brand]))Hi Rena
Sorry it took so long. Required a little extra thought and then the penny dropped... a lot of the challenge was arising from using Material Table "Brand" as the Slicer given that all of the measures were reflecting the Volume Table "Brand". I was running into the same issues until this point.
I am hoping that the attached is what you are after. I have started from a blank canvas in the attached so please let me know how it all goes!
Below is an output:
Hope this helps!
Theo 🙂
14 Replies
- TheoC
Community Champion
Hi Rena
I hope you're doing well!
I created some dummy data based on my understanding of your post. But please let me know if the below is what you are after. Just note that where no selection has been made in the slicer, I have just added "Choose From Slicer" to alert the user to select an option from the slicer. Otherwise, this can be adjusted to whatever you'd like:
Once a user selects from the slicer, the results change accordingly. Again, I've just added dummy data on the basis of what I understood. Please let me know if I've misunderstood.
TotalVolumeForSelectedPlants = VAR _SelectedBrand = SELECTEDVALUE ( 'Material Table'[Brand] ) VAR _PlantsWithSelectedBrand = CALCULATETABLE ( VALUES ( 'Volume Table'[Plant] ) , 'Volume Table'[Brand] = _SelectedBrand , 'Volume Table'[Actual] > 0 ) VAR _result = CALCULATE ( [Sum of Actual Volume] , 'Volume Table'[Plant] IN _PlantsWithSelectedBrand, ALL ( 'Material Table'[Brand] ) ) RETURN IF ( ISBLANK ( _result ) , "Choose From Slicer" , _result )Hope this helps!
Theo 🙂
RenaOne more help if you don't mind.
Same sheet. Now I am adding the last part which is pulling in one more table and adding values specifically from that table that already have a measure on them.
New table is Spend by Plant
I am taking each plant and multiplying their spend by the volume of the brand from above. I have that working fine.
Example: Plant 1 - Total spend is $10MM with 42% ABC volume for adjusted spend of $4.2MM
Plant 2 - Total spend is $3MM with 74% ABC volume for adjusted spend of $2.2MM
Plant 3 - Total spend is $5MM with 1% ABC volume for adjusted spend of $50K
Total plant spend is $18MM and total volume between all 3 plants is 42% for product ABC
Currently it is adding all of their spend up ($18MM and multiplying that number by 42% total volume) and giving me a $7.5MM value but I need it to add up each plant individually for the $6.4MM.
No matter what I write I am totaling the volume and the spend and dividing rather than adding the already divided totals.
- Rena
So sorry to keep bothering you. But I put that in and then when I selected all brands I end up with a syntax error saying multiple values where only one value was expected. My old formula works for multiple brands and this formula only works for single brands. Is there3 a way to change the return so that if it is multiple brands I can put in my old formula and single brands can leave (Result)?
- Rena
Got this figured out - leaving it here in case someone else needs it in the future
Brand Volume Test =VAR SelectedBrand = SELECTEDVALUE('Material Map'[Brand])VAR TotalBrands =TREATAS(VALUES('Material Map'[Brand]),'Volume Lock'[Brand])VAR PlantwithBrands =CALCULATETABLE(VALUES('Volume Lock'[Plant Number]),'Volume Lock'[Brand] IN TotalBrands,'Volume Lock'[Volume]>0)RETURNCALCULATE(SUM('Volume Lock'[Volume]),'Volume Lock'[Plant Number] IN PlantwithBrands,ALL('Material Map'[Brand]))
- Rena
Worked perfectly. Thank you so much!
- Rena
I have multiple tables.
One table maps the material used to the brands
One table holds the actual volume produced by brand, material, and plant
One table holds the actual spend by plant
I have 30 total plants. Each plant produces a different set of brands, but multiple plants produce the same brand.
On my slicer, I am selecting a brand. When I select that brand, I am getting how much total the company spends to produce that brand based on volume at the plant level.
Example: Brand A is produced in Plants 1, 2, and 3.
Plant 1 spends $10MM and 42% of it's production is Brand A so the calculation [account spend]*[volume %] says $4.2MM
Plant 2 spends $4MM and 1% of its production is Brand A so the calculation says $40k
Plant 3 spends $3MM and 75% of its production is Brand A so the calculation says $2.3MM
The problem is my total is giving me 45% of $17MM for $7.7MM instead of adding the 3 separate measures up which should total $6.5MM.
Currently I use this basic DAX to calculate the [account spend] (volume spend is the same but just looking at volume instead of spend)
VAR TotalBrands =TREATAS(VALUES('Material Map'[Brand]),'Volume Lock'[Brand])VAR PlantwithBrand =CALCULATETABLE(VALUES('Volume Lock'[Plant Number]),'Volume Lock'[Brand] IN TotalBrands,'Volume Lock'[Volume]>0)VAR Result =CALCULATE([Account Spend],'Actual Spend'[Plant] in PlantwithBrand)RETURNResultAny ideas?- HotChilli
Sounds like a DAX totals issue.
I'm not really a fan of these overly-verbal descriptions of problems. There's too much information and too much left unsaid.
If you provide a sample pbix and show the desired result, you'll get a quicker answer.
Hello, HotChilli,thanks for your concern about this issue.
And I would like to share some additional solutions below.Hi,Rena .I am glad to help you.
Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions.
Thank you very much for your understanding and support of Power BI.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian
