topic Re: Measure calculation using different columns based on a value in DAX Commands and Tips

Measure calculation using different columns based on a value

aallman — Thu, 10 Nov 2022 23:49:53 GMT

This is going to be confusing, I apologize in advance. I will try to be as clear as possible.

I am trying to create a measure I can use in a visual table that can be filtered by the user. I want that visual to look something like this where $Value is the measure I am trying to create:

OrderNbr	ShipComplete	$Value
12345	Yes	$12,000
67891	No	$2,000

My question is, is it possible to have the measure sum different column values based on if the order is ShipComplete=Yes or No? In my raw data, each order will have several lines all with differnent $LineValues. In my visual, I want the measure to Sumx(ALL( $LineValues)) IF the order is ShipComplete=Yes regardless of any filters the user applies. If the order is ShipComplete=No I want the measure to only sum the $LineValues of lines that the user filters for.

The filters will usually only apply to certain line items on an order so if a filter is applied, the order could get split up in my visual. For example, if the filters result in only 2 of the 5 line items on an order and the order is ShipComplete=Yes then I want the visual to show the sum of all 5 line item values but if the order is ShipComplete=No then I want the visual to show the sum of only the 2 line item values.

UPDATE: This is even more complex than I originally thought. My data has 3 levels to it: Order, TopLevel, Item. Each Item has a price associated with it and also have the values which the user will be filtering based on in my data. I want the resulting visual to show a sum on the Order level (as seen in above table). If the order is ship complete I want to see the sum of all Item values in that order regardless of filters. If the order is not ship complete but the item has a TopLevel, I want to see the sum of all Item values in that TopLevel. Otherwise I only want to see the sum of Item values that the user has filtered for. Below is a better example of my raw data set where Color is what the user will filter the visual on.

Order#	TopLevel#	Item#	Ship Complete	Item$	Color
1	100	101	No	5	red
1	100	102	No	7	green
1	100	103	No	5	green
2		104	Yes	10	red
2		105	Yes	4	green
3	200	106	No	5	red
3	200	107	No	3	green
3	201	108	No	5	red
3	201	109	No	3	green
3		110	No	10	green

From this data I want a visual that results in exactly the following (assuming the user filters for "red")

Order#	Ship Complete	Total Value
1	No	17
2	Yes	14
3	No	16

Is this even possible??

Re: Measure calculation using different columns based on a value

WinterMist — Fri, 05 Aug 2022 21:26:10 GMT

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

Re: Measure calculation using different columns based on a value

WinterMist — Fri, 12 Aug 2022 15:04:37 GMT

aallman

Were you able to get things working?

If not, please let me know.

If so, please consider marking this as a solution.

Regards,
Nathan

Re: Measure calculation using different columns based on a value

aallman — Thu, 10 Nov 2022 23:50:43 GMT

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!

Re: Measure calculation using different columns based on a value

WinterMist — Fri, 11 Nov 2022 18:02:45 GMT

aallman

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