Forum Discussion

Uziel_'s avatar
Uziel_
New Member
3 years ago

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

  • 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