topic Re: Creating Running total chart using DAX in DAX Commands and Tips

Creating Running total chart using DAX

vai — Mon, 08 Apr 2024 19:41:00 GMT

Hi I created a running total chart using measured from DAX and connecting them to a master calendar table query. Initially, these formulas worked fine (picture 1) when I set them up as a single directional relationships in the model view. But since I want to use the filters, I decided to change them to bi-directional so that it reflects appropriately, but then all my target lines go wonky (picture2). Is there something I am missing, or can do better to set this feature up?

Re: Creating Running total chart using DAX

lbendlin — Mon, 08 Apr 2024 21:38:32 GMT

what was the rationale for the bidirectional link?

Did you use the Running Total Quick Measure?

Re: Creating Running total chart using DAX

vai — Mon, 08 Apr 2024 22:43:06 GMT

For the first picture, if i click on the filters, only the top two (straight) target lines moved but not the other running total lines. And upon research online, I read this would be successful if my relationship was bidirectional.

As for running total, I did not use quick measure. I created my own measures. Example:

RTYTD =

VAR MaxDate = MAX('Calendar Table'[Dates])

RETURN

CALCULATE(SUM('Table1'[Potential Cost Savings]), FILTER(ALLNOBLANKROW('Table1'), 'Table1'[Actual Date] <= MaxDate && 'Table1'[Actual Date] <> BLANK()))

I figured out most of the solution in the last few hours, where if i add allcrossfiltered bit to the measure, the lines are corrected to what it shouldve been line in the picture 1. But if i clear filters, the two target lines (green & blue) still show wonky like picture 2. The problem is I dont know how to add the cross-filter logic since they are not really measures, they are just static numbers being pulled from power query that I created with target amt.

Re: Creating Running total chart using DAX

lbendlin — Tue, 09 Apr 2024 15:11:34 GMT

you can make them measures with a fake aggregation like MIN() or MAX() or AVERAGE()