Forum Discussion
Measure calculation using different columns based on a value
Hello aallman
OK. Hopefully I understand correctly.
1) Create measure "01 Selected Value".
This measure determines which value is selected for the "Ship Complete" slicer:
- Yes, No or (Blank)
2) Create measure "02 Sum EXCLUDE Filters".
This will SUM all values while ignoring the filter selection, as you requested.
3) Create measure "03 Sum INCLUDE Filters".
This will SUM all values within the current FC (Filter Context), as you requested.
4) Create measure "04 Decision".
This is where the magic happens.
IF Ship Complete = Yes
THEN it returns the SUM of everything (excluding filters)
ELSE it returns the SUM of everything (within the current FC)
You can, of course, put all 4 of these measures into VARS, and combine them into a single measure if you wish.
However, for testing purposes, I like to place each one of them in a card to see what happens when I select values within a slicer, as follows.
Scenario 1 - Ship Complete = (Blank)
Scenario 2 - Ship Complete = Yes
Scenario 3 - Ship Complete = No
Hopefully this is helpful to you!
Regards,
Nathan
This is super helpful but it didn't quite work for me. I still need each order's line values summed separately to get the order's total for each order in the visual. I then would like to be able to see all those values in one column so I can get a grand total.
Does that make sense?
I added additional info to the original post!
Regarding the UPDATE to requirements, I believe I am close. For summary, I have outlined the 3 scenarios here:
NOTES:
SC = Ship Complete
FC = Filter Context
Only Scenario #3 should be affected by the Color slicer
since it is the only scenario affected by FC
S1) IF SC = Yes
THEN SUM Item$ values per Order# (EXCLUDE FC)
S2) IF (SC = No) AND (TopLevel# exists)
THEN SUM Item$ values per TopLevel# (EXCLUDE FC)
S3) ELSE
SUM Item$ values (INCLUDE FC)Create a calculated column [Item CC] which calculates the sum of [Item$] for each [Order#] & [TopLevel#]
Add the calculated column to the visual.
NOTE: In the "Desired Result" visual, you will notice I have added "TopLevel#", which was not present in your UPDATED requirements. As long as this is present, the visual seems to work. But if it is removed, undesired behavior occurs.
You can see that Item CC is calculating correctly for each Order# & TopLevel# when no slicer value is selected. (Screenshot above)
IF slicer Color = green THEN the 3 requirement scenarios are satisfied.
S1) For Order #2, SC = Yes. Therefore, the sum of values for Order #2 is displayed, ignoring the FC. Result = 14.
S2) For Order #1 & #3, SC = No AND TopLevel# exists (partial for #3). Therefore, the sum of values for Order #1 & #3 is displayed per each TopLevel#, ignoring the FC.
- RESULT for Order #1 & TopLevel# 100 = 17
- RESULT for Order #3 & TopLevel# 200 = 8
- RESULT for Order #3 & TopLevel# 201 = 8
S3) There is only 1 row which meets Scenario 3, where SC = No & TopLevel# does NOT exist. For this row, the requirement states that it should SUM item values WITHIN THE CURRENT FC. Since Color currently is set to Green, and this row is Green, this row should appear & it does.
IF slicer Color = red THEN the 3 requirement scenarios are satisfied as well.
S1) Order #2 remains at 14, ignoring FC.
S2) Multiple items:
Order #1 & TopLevel# 100 remain at 17, ignoring FC.
Order #3 & TopLevel# 200 remain at 8, ignoring FC.
Order #3 & TopLevel# 201 remain at 8, ignoring FC.
S3) The single row where SC = No AND TopLevel# is NULL has now disappeared correctly, because it is green and should be affected by the slicer (FC), which it is.
Hopefully this is helpful for you & adding TopLevel# to the "Desired Result" visual is not a problem for you.
Regards,
Nathan
