Forum Discussion
Uziel_
3 years agoNew Member
Measure retuns the wrong amount
I am using PBI Desktop and I have a DAX formula that works great until I try to get an average.
KPI =
var a =
IF(
[Rollup] && HASONEVALUE(tKPI[Category]),
SUMX(
ADDCOLUMNS(
CROSSJOIN(
SUMMARIZE(
tKPI,
tKPI[Category],
tKPI[Group],
tKPI[Metric],
tKPI[Category - Group - Metric],
tKPI[Type]
),
SUMMARIZE(
tDate,
tDate[Date]
)
),
"Value", var a = FIRSTNONBLANK(tKPI[Category],1)
var b = FIRSTNONBLANK(tKPI[Metric],1)
var c = SWITCH(
TRUE,
a = "Unbilled Tuition", CALCULATE([UB Sum]),
a = "DFE", CALCULATE([_DFE Actual]),
b = "Total Inquiries", CALCULATE(SUM(tInquiry[Count])),
b = "Average Inquiries", CALCULATE(AVERAGE(tInquiry[Count])),
b = "Credit Memo Amount", CALCULATE(SUM(tCreditMemo[Amount])),
b = "Credit Memo Count", CALCULATE(SUM(tCreditMemo[CM Count])),
b = "Credit Memo Average", var x = CALCULATE(SUM(tCreditMemo[Amount]))
var y = CALCULATE(SUM(tCreditMemo[CM Count]))
var z = DIVIDE(x, y)
return z,
a = "Enrollment", CALCULATE(SUM(tFTE[Count]))
)
return c
),
[Value]
)
)
var b = IF(a, a)
return b
The problem lies here.
b = "Credit Memo Average", var x = CALCULATE(SUM(tCreditMemo[Amount]))
var y = CALCULATE(SUM(tCreditMemo[CM Count]))
var z = DIVIDE(x, y)
return z,
90 / 3 = 30 (at least that's what my calculator tells me...), but it returns 60. Changing the return to use x returns 90 and changing z to y return 3, but dividing x by y gives 60.
Does anyone know why?
1 Reply
- pratyashasamalMemorable Member
Hi Uziel_ ,
could you please share some background for this issue . If possible please the pbix file so that it will be easier to understand the root cause if the issue .
Thanks ,
Pratyasha