How to use the cumulative total of a quarter as a reference line in a line chart power bi

[sajal161292]

Hi,

I have a requirement to create a sales report.I need to prepare a report as per weeks in a quarter against different KPIs.

There is a field x.I need to perform the cumulative total of x in a quarter and then use the cumulative value as a baseline in  line chart.

The cumultaive total should remian constant throughout regardless of the weeks being present in the chart while plotting.

I used a measure as:

measure = TOTALQTD([x],'Calendar'[Date])

But it is not giving me correct result.

Please help me in achieving the same.

[Seward12533]

Can you confirm the relationship between the tables? Also are you using the DATE from the Calendar Table or your Fact Table in your visual. 

[sajal161292]

Hi,

There are only 2 tables:

Calendar

Sales 

So all the sales data is coming from only 1 table.I am using DATE from the Calendar table in the visual.

[Seward12533]

Ahhh looking at it again now I understand.  This is the way TOTALQTD, TOTALMTD etc work.  If you want TOTAL for the QUARTER you may need to write your own measure. Something like this shoudl work assuming you have YEAR-QTR or someting similar in your data table. If not its easy enough to add with a calculated column using FORMAT.

If you want the period to total for whatever period you display on the visual use this : 

TOTAL Period = CALCULATE([x],ALL(Date),FILTER(ALL(Date[Date]),Date[Date]<=MAX(Date[Date]&&Date[Date]>=MIN(Date[Date]) // calculate Total for displayed period

IF you want to display in Days but show quarter then something like this
TOTAL QTR = VAR Q_Check = LOOKUPVALUE(date[YEAR_QTR],date[date],MAX(date[date]) RETURN
CALCULATE[x],ALL(DATE),FILTER(ALL(date[Date]),date[YEAR_QTR]=Q_Qcheck)

[sajal161292]

Hi,

I am able to get the total quantity for a quarter but the issue is that I need to use this total quarter value as a reference value in my line chart visual.

I need to make this value as  a constant.

But the problem is I am plotting this value against weeks due to which it is again getting distributed as per weeks as:

But what i would like to see as:

This value should not vary with respect to weeks.

So is there any way to achieve this using dax in power bi?

[Seward12533 · Accepted solution]

The second measure template I provided will do that. If its not working check the relationships between your tables.

Here is a sample workbook - https://1drv.ms/u/s!AuCIkLeqFmlhhJkDWA_PWzQKhjGsUg

Total QTD = TOTALQTD([Sales],DateDim[Date])

Total for QTR = VAR QCheck = LOOKUPVALUE(DateDim[YEAR_QTR_Text],DateDim[Date],MAX(SalesData[Date])) RETURN 
    CALCULATE([Sales],ALL(DateDim),FILTER(ALL(DateDim),DateDim[YEAR_QTR_Text]=QCheck))

[sajal161292]

Yes..it is working fine!!

Thank you for your help!!

[sajal161292]

Hi,

This solution does not work if there any blanks in the data:

As given in the example if there are any blanks in the VALUE field then the measure also is blank.But I would like it to show as 100 in all the weeks of a quarter(constany value throughout)