Create a Moving Range Measure

[ankit]

Hi @jprlimey

Incorrect value for “Totals” is the most annoying thing for me with Power BI Why can’t they provide an option to just sum up all the records for that measure in visual.

Anyways based on your formula, problem is for “Total”, all Review No’s are available and as you are using SelectedValue function it will return Blank(), thus doing Sum over all the Reveiw No.There is no single function that can help.

As you rightly mentioned you need to create Virtual table in calculation and along with HASONEVALUE() or ISINSCOPE(). Refer to below article, it has got lots of details.

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

Regarding your second problem, try below. Replace MonthnYear with ReviewNo

MinValue =
VAR MinRec =
    CALCULATE ( MIN ( Dates[MonthnYear] ), ALLSELECTED ( Dates[MonthnYear] ) )
RETURN
    IF ( SELECTEDVALUE ( Dates[MonthnYear] ) = MinRec, BLANK (), [Total Sales] )

Please share PBIX file if you want detailed answer on 1st Problem.

Thanks
Ankit J