cancel
Showing results for
Did you mean:
Frequent Visitor

## Measure calculation using different columns based on a value

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??

4 REPLIES 4
Responsive Resident

Were you able to get things working?

If not, please let me know.

If so, please consider marking this as a solution.

Regards,
Nathan

Responsive Resident

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

Frequent Visitor

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?

Responsive Resident

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors