Sum the averages for the total

[PabloML]

Dear all,

I'm having a doubt on how calculate a measure that for a single-item is the average, but for the totals it needs to sum the average of each cattegory. 

The measure it's:

Target YTD =

var _Year = SELECTEDVALUE(Calendar[Year_Especial])

var _Month = SELECTEDVALUE(Calendar[Month_Especial])

var _MonthOrder =

MAXX(

    FILTER(ALL(Calendar), AND(Calendar[Month_Especial] = _Month, Calendar[Year_Especial]= _Year)),

    Calendar[Year_Month_Order]

)

var _value =

CALCULATE(

    AVERAGE(Fact_Table[TARGET]),

    FILTER(ALL('Calendar'),AND(Calendar[Year_Especial] = _Year, Calendar[Year_Month_Order]<= _MonthOrder))

)

var _table = VALUES(Fact_Table[KPI/ITEM])

var _valueTotal =

ADDCOLUMNS(

    _table,

    "@Value", _value

)

RETURN

IF(

    HASONEVALUE(Fact_Table[KPI/ITEM]), _value,

    SUMX(_valueTotal, [@Value])

)

The next table is the expected result, where the Subtotal are the sum of the averages for each category and the Total is the sum of Subtotal, but I don't get the Subtotal correct neither the Total.

Anyone has a clue about what I'm doing wrong?

Thanks. 

Main KPI             KPI/ITEM    Target YTD

KPI 1	KPI 1.1	2
KPI 1	KPI 1.2	3
KPI 1	KPI 1.3	4
SubTotal KPI 1		9
KPI 2	KPI 2.1	2
KPI 2	KPI 2.2	5
KPI 2	KPI 2.3	4
SubTotal KPI 2		11
Total		20

[PabloML]

Many thanks, Jayleny. It works great. Now, I need to understand why executing the code for "single-item" within the main calculate, where a virtual table is created, will work, and not the way I was doing it.

As said, many thanks.

[v-jialongy-msft · Accepted solution]

Hi @PabloML 

Try the following dax

Target YTD =
VAR _Year = SELECTEDVALUE(Calendar[Year_Especial])
VAR _Month = SELECTEDVALUE(Calendar[Month_Especial])
VAR _MonthOrder = MAXX(
    FILTER(
        ALL(Calendar),
        Calendar[Month_Especial] = _Month && Calendar[Year_Especial] = _Year
    ),
    Calendar[Year_Month_Order]
)
VAR _CategoryContext = VALUES(Fact_Table[KPI/ITEM])
RETURN
IF(
    HASONEVALUE(Fact_Table[KPI/ITEM]),
    CALCULATE(
        AVERAGE(Fact_Table[TARGET]),
        FILTER(
            ALL(Calendar),
            Calendar[Year_Especial] = _Year && Calendar[Year_Month_Order] <= _MonthOrder
        )
    ),
    SUMX(
        _CategoryContext,
        CALCULATE(
            AVERAGE(Fact_Table[TARGET]),
            FILTER(
                ALL(Calendar),
                Calendar[Year_Especial] = _Year && Calendar[Year_Month_Order] <= _MonthOrder
            )
        )
    )
)

If the above dax can't help you solve your problem, please provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.