Forum Discussion

klehar's avatar
klehar
Helper V
4 years ago
Solved

Running Total at drill down doesnt work

Hi,   I have a bar + line chart that shows Year and Quarter at one level and Week at drill down level. I want that the running total line that shows right values at the year quarter level shold al...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi klehar your issue is that you are making the calculation only at quarter level so you are not abble to get the week level calculation correct.

     

    Try to do the following steps:

    • Create a new column:
    Year + Q + W = 'Table'[Year Quarter]&'Table'[Weak]

     

    • Change your measure to:
    Profit running total in Year Quarter = 
CALCULATE(
	SUM('Table'[Profit]),
	FILTER(
		ALLSELECTED('Table'[Year Quarter], 'Table'[Weak],'Table'[Year + Q + W]),
		ISONORAFTER('Table'[Year + Q + W], MAX('Table'[Year + Q + W]), DESC)
	)
)

     

    Result below and in attach PBIX file.

     

     

MFelix's avatar
MFelix
Super User
4 years ago

Hi klehar your issue is that you are making the calculation only at quarter level so you are not abble to get the week level calculation correct.

 

Try to do the following steps:

  • Create a new column:
Year + Q + W = 'Table'[Year Quarter]&'Table'[Weak]

 

  • Change your measure to:
Profit running total in Year Quarter = 
CALCULATE(
	SUM('Table'[Profit]),
	FILTER(
		ALLSELECTED('Table'[Year Quarter], 'Table'[Weak],'Table'[Year + Q + W]),
		ISONORAFTER('Table'[Year + Q + W], MAX('Table'[Year + Q + W]), DESC)
	)
)

 

Result below and in attach PBIX file.