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
AndriaC7
Regular Visitor

Measure to calculate percentage differently based on value

I have a measure that needs to calculate total percentage of values; however, the calculation is different for the types of items within the measure. I am having difficulty calculating this as HASONEVALUE is returning nothing (because there are multiple values) and FIRSTNONBLANK is only returning the first value.

 

Each inventory item is classified by type - new, used, etc.
New inventory percentage is calculated by adding three columns together and dividing by a separate field, ex: SUM(Prod_Cost) + SUM(Ship_Cost) + SUM(Storage_Cost) /  SUM(Shipments)
Used inventory percentage is calculated by dividing one column by a separate field, ex: SUM(Total_Cost) / SUM(Shipments)
I have tried the following queries:

VAR N_AEC = DIVIDE((SUM(Prod_Cost) + SUM(Ship_Cost) + SUM(Storage_Cost)),SUM(Shipments),0)
VAR U_AEC = DIVIDE(SUM(Total_Cost),SUM(Shipments),0)
RETURN SWITCH(TRUE(),FIRSTNONBLANK(CoreType[CoreID],1) = "N",N_AEC,U_AEC)

 

Above query returns only the percentage of the item it first hits (example, multiple manufacturers in inventory table, only returns percentage for first manufacturer in alpha order) ^

 

VAR N_AEC = DIVIDE((SUM(Prod_Cost) + SUM(Ship_Cost) + SUM(Storage_Cost)),SUM(Shipments),0)
VAR U_AEC = DIVIDE(SUM(Total_Cost),SUM(Shipments),0)
RETURN IF(HASONEVALUE(CoreType[CoreID]),SWITCH(TRUE(),VALUES(CoreType[CoreID]) = "N",N_AEC,U_AEC))

 

Above query returns blank - no data ^

 

When I add N_AEC and U_AEC together, I am getting a percentage over 100%. Somehow I need the measure to know that if the item in the inventory table is a New CoreID then do the formula for N_AEC, else do the formulat for U_AEC.

1 REPLY 1
v-frfei-msft
Community Support
Community Support

 Hi @AndriaC7 ,

 

Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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.