Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |