Grand Total Incorrect Due to Division

[domacisaac]

 

 

Hi, I have been struggling to ensure that I get an accurate subtotal for this Measure I hae created. In essence, I have to divide the day over day change in expected sales by the number of days remaining in the month. This works accurately for the delivery months but not for the subtotal. I know the issue is that it is pulling an average value, like 24 (shown below) 

Here is my dax:

DTD Daily Sales =
VAR maxdate= SELECTEDVALUE('DIM vDate'[Display Date])
var max2date= SELECTEDVALUE('DIM vDate'[Prev Day])
VAR DailySales_a = CALCULATE(sum('FACT vProfitAndLossMonthly'[Sales]), 'FACT vProfitAndLossMonthly'[Date]= maxdate)
VAR DailySales_b = CALCULATE(sum('FACT vPowerProfitAndLossMonthly'[Sales]),'FACT vPowerProfitAndLossMonthly'[Date] = max2date)
VAR XR = CALCULATE(AVERAGE('FACT vProfitAndLossMonthly'[Date]),'FACT vPowerProfitAndLossMonthly'[Date Diff]= maxdate)
VAR dodchange = CALCULATE(MW_a-MW_b)/XR
return dodchange

How do I get this to effectively sum these values instead of dividing the overall change by 24?

Thanks in advance

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

Measure 1 = if(hasonevalue('DIM vDate'[Month name]),[DTD Daily sales],SUMX(VALUES('DIM vDate'[Month name]),[DTD Daily sales]))

Ensure that Year and Month name are dragged from the Calendar Table.

Hope this helps. 

[amitchandak]

@domacisaac , Change return like

return

Sumx(Summarize( 'DIM vDate','DIM vDate'[Year],'DIM vDate'[Month], "_1",dodchange), [_1])

[domacisaac]

Hi there, I am getting an error as the year and month that is being displayed is coming from the same table as the sales.
I changed the return statement to:

return Sumx(Summarize( 'FACT vProfitAndLossMonthly','FACT vProfitAndLossMonthly'[Delivery Date].[Year],'FACT vProfitAndLossMonthly'[Delivery Date].[Month], "_1",dodchange), [_1])

But am unfortunately getting this: