Forum Discussion

domacisaac's avatar
domacisaac
New Member
4 years ago
Solved

Grand Total Incorrect Due to Division

 
 
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

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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. 

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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's avatar
    amitchandak
    Super User
    4 years ago

    domacisaac , Change return like

    return

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

    • domacisaac's avatar
      domacisaac
      New Member
      4 years ago

      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: