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 🙂
Resolver II
One 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.
- TheoC
Hi Rena
Sorry for late reply. Just wanting to confirm you figured the second question out?
If not, let me know and I can assist.
Thanks heaps,
Theo
- Rena
No. I am still stuck on trying to get the plant costs to mutiply by the volume individually and then add up. I can only get it to multiply the total plant costs by the volume %.
Here is what I am currently using
- TheoC
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 🙂