Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Total Value for Table on left should be 6.71, not 4.52. How can I achieve this, here is my measure applied:
ft per = (((AVERAGEA('51039 BI'[Feet Installed (ft)])) / (AVERAGEA([Total Feet (ft)])))*100)
Thank you
Solved! Go to Solution.
Try this measure:
_FeetPer =
VAR __feetPer =
AVERAGEX(
YourTable,
VAR __feetInst = IF(YourTable[FeetInstalled] <> 0, YourTable[FeetInstalled])
VAR __feetTotal = IF(YourTable[TotalFeet] <> 0, YourTable[TotalFeet])
RETURN
DIVIDE(__feetInst, __feetTotal, BLANK()) * 100
)
RETURN
IF(
HASONEVALUE(YourTable[LaborCode]),
__feetPer,
DIVIDE(
__feetPer,
CALCULATE(
DISTINCTCOUNT(YourTable[LaborCode]),
ALLSELECTED(YourTable)
),
BLANK()
)
)
Pete
Proud to be a Datanaut!
Hi @350z ,
On what basis are you expecting the result to be 6.71? From what I can see, (102.61 / 2268.74) * 100 = 4.52.
Can you explain the logic you're using to get to the 6.71 figure please?
Pete
Proud to be a Datanaut!
4.52 would be correct for an overall percentage that I'll be using on another visual. 6.71 should be the mean.
(20.13+0+0) = 20.13...
3/20.13 = 6.71
The table on the left will always give a total overall percentage even if specific, not all, selections are made in a slicer?
Try this measure:
_FeetPer =
VAR __feetPer =
AVERAGEX(
YourTable,
VAR __feetInst = IF(YourTable[FeetInstalled] <> 0, YourTable[FeetInstalled])
VAR __feetTotal = IF(YourTable[TotalFeet] <> 0, YourTable[TotalFeet])
RETURN
DIVIDE(__feetInst, __feetTotal, BLANK()) * 100
)
RETURN
IF(
HASONEVALUE(YourTable[LaborCode]),
__feetPer,
DIVIDE(
__feetPer,
CALCULATE(
DISTINCTCOUNT(YourTable[LaborCode]),
ALLSELECTED(YourTable)
),
BLANK()
)
)
Pete
Proud to be a Datanaut!