Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aallman
Helper I
Helper I

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:

OrderNbrShipComplete$Value 
12345Yes$12,000
67891No$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 CompleteItem$Color
1100101No5red
1100102No7green
1100103No5green
2 104Yes10red
2 105Yes4green
3200106No5red
3200107No3green
3201108No5red
3201109No3green
3 110No10

green

 

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

Order#Ship CompleteTotal Value
1No17
2Yes14
3No16

 

Is this even possible??

4 REPLIES 4
WinterMist
Impactful Individual
Impactful Individual

@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

WinterMist
Impactful Individual
Impactful Individual

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)

WinterMist_0-1659733854246.png

 

2) Create measure "02 Sum EXCLUDE Filters".

This will SUM all values while ignoring the filter selection, as you requested.

WinterMist_1-1659733970025.png

 

3) Create measure "03 Sum INCLUDE Filters".

This will SUM all values within the current FC (Filter Context), as you requested.

WinterMist_2-1659734080332.png

 

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)

WinterMist_3-1659734240552.png

 

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)

WinterMist_4-1659734384495.png

 

Scenario 2 - Ship Complete = Yes

WinterMist_5-1659734445613.png

 

Scenario 3 - Ship Complete = No

WinterMist_6-1659734615115.png

 

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!

WinterMist
Impactful Individual
Impactful Individual

@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#]

 

WinterMist_0-1668188017909.png

 

WinterMist_1-1668188114242.png

 

Add the calculated column to the visual.

 

WinterMist_2-1668188324113.png

 

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.

WinterMist_3-1668189088706.png

 

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.

WinterMist_4-1668189282575.png

 

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors