Forum Discussion
Resolver IIMeasure to remove one filter but add another
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 🙂
Community ChampionHi 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])) - TheoC
Hi Rena
I'm so sorry for late reply! Weekend followed by a public holiday here in Australia so only just got back on now. Did you end up sorting this out?
If not, you can use "IFERROR ( [Measure 1] , [Measure 2] )" to work around this. Otherwise, you should be able to update the end of the measure (after where it says RETURN) with "IFERROR ( ...... , [Measure 2])" The ...... represents whatever the DAX code is after the "RETURN" in the measure itself.
Please let me know if that makes sense or you need further help!
Theo 🙂