topic Row Subtotal of Measure in Pivot table is empty in DAX Commands and Tips

Row Subtotal of Measure in Pivot table is empty

Columnist — Thu, 26 Oct 2023 18:54:16 GMT

Dear Community,

I am strugling to understand and fix the issue of empty subtotals for the measures I created in the Power Pivot model.

Please see below the view of the Pivot table:

The YoY measure returns same week previous year sales value (eg. Actual Booking in week 2 of year 2023 ($623K) are reflected as YoY Actual Bookings for week 2 of year 2024).

YoY measure synthax:

YoY:=CALCULATE([Actual Bookings],
(FILTER(ALL(T_Calendar),
T_Calendar[Fiscal Year]=IF(HASONEVALUE(T_Calendar[Fiscal Year]), VALUES(T_Calendar[Fiscal Year]))-1&&
T_Calendar[Fiscal Week]=IF(HASONEVALUE(T_Calendar[Fiscal Week]), VALUES(T_Calendar[Fiscal Week])))))

Could you please advise why the measure is present in the row of the Pivot table for each week but not visible in Subtotal while other rows in Pivot table are there in Subtotal? What should I do to fix it?

Thank you in advance!

Re: Row Subtotal of Measure in Pivot table is empty

wdx223_Daniel — Fri, 27 Oct 2023 06:38:18 GMT

in subtotal column, HASONEVALUE was not fullfilled, it will give a blank, ie false. so there is not dates included.

Re: Row Subtotal of Measure in Pivot table is empty

Columnist — Sun, 29 Oct 2023 13:13:40 GMT

Thanks, wdx223_Daniel. Appreciate if you could suggest what I should change in formula to make it work correctly.

Re: Row Subtotal of Measure in Pivot table is empty

wdx223_Daniel — Mon, 30 Oct 2023 05:57:33 GMT