locked
Calculating QTD AVG Sales RRS feed

  • Question

  • Greetings.

    I'm attempting to calculate QTD Avg Sales and need some assistance to tweak the logic.

    First, let me show what I need data I have and what the end result should be in Excel.

    In essence I divide, QTD Aggregrate / QTD Days in spreadsheet and get the expected result as highlighed in yellow which is my QTD AVG Sales.

    Now that I've tried it in Power Pivot, I need help with get the correct QTD AVG Sales in Power BI. The first issue encountered is on measure"QTD Aggregate"; it is not giving the correct value. It should sum up measure "Monthly Aggregrate" for the specific quarter, but instead it is giving me different values than expected.


    I've also included my measure to get a clear picture of what I am doing in the process.

    MTD Sales = CALCULATE([Current Month Sales])

    Monthly Aggregate = CALCULATE([MTD Sales]*[MTD Days])

    MTD Days = COUNT(Dates[Date])

    QTD Aggregate = CALCULATE([Monthly Aggregate],DATESQTD(Dates[Date]))

    QTD Days = TOTALQTD([MTD Days],Dates[Date])

    QTD Sales = CALCULATE([MTD Sales],DATESQTD(Dates[Date]))


    The second issue is performing calculation QTD AVG Sales. 

    I've triedd various ways to calculate QTD Sales, but in one scenario the results become Infinity if I set up my measure like this:

    Test 1
    
    QTD AVG Sales = CALCULATE([QTD Aggregrate]/[QTD Days],DATESQTD(Dates[Date]))

    Test 2 

    This sums up MTD Sales for each quarter, but doesn't give me the expected QTD Sales result.

    QTD AVG Sales = CALCULATE([MTD Sales],DATESQTD(Dates[Date]))

    I would greatly appreciate if someone may  please help me and offer guidance as to how to get measure QTD AVG Sales to output the expected results as they are presented in the Excel screenshot. 


    Thank you in advance for taking the time to review and any feedback you may have for me.

    Cheers,

    Jeannette





    • Edited by Jeannette_81 Monday, May 7, 2018 9:08 PM Adding content to me more clear
    Monday, May 7, 2018 8:17 PM

Answers

  • Hi Jeannette,

    Thanks for your question.

    I will not suggest you to use you measure [MTD Sale], Please use the original measure [Total Sales]. Assuming you have created a SUM measure as below:
    [Total Sales] =Sum(Sales[Amount])
    And you have created a dates table with Month, Quarter, Year and so on.

    Create a measure QTD Sales as below:

    QTD Sales =
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( Dates ),
            Dates[Date] <= MAX ( Dates[Date] )
                && Dates[Quarter] = MAX ( Dates[Quarter] )
                && Dates[year] = MAX ( Dates[year] )
        )
    )


    Create a measure QTD Days as below:

    YTD Days =
    COUNTROWS (
        FILTER (
            ALL ( Dates ),
            Dates[Date] <= MAX ( Dates[Date] )
                && Dates[Quarter] = MAX ( Dates[Quarter] )
                && Dates[year] = MAX ( Dates[year] )
        )
    )

    In the end, create QTD AVG Sales as below:

    QTD AVG Sales =
    DIVIDE ( [QTD Sales], [QTD Days] )

    Please refer to a similar thread you posted before:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5d12711b-27a2-4360-96fb-4df23090c334/calculating-ytd-average-balances-using-exsiting-column-mtd-average-balance?forum=sqlkjpowerpivotforexcel#91b978f0-8461-48b7-b198-be47dd766913


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 8, 2018 3:14 AM

All replies

  • Hi Jeannette,

    Thanks for your question.

    I will not suggest you to use you measure [MTD Sale], Please use the original measure [Total Sales]. Assuming you have created a SUM measure as below:
    [Total Sales] =Sum(Sales[Amount])
    And you have created a dates table with Month, Quarter, Year and so on.

    Create a measure QTD Sales as below:

    QTD Sales =
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( Dates ),
            Dates[Date] <= MAX ( Dates[Date] )
                && Dates[Quarter] = MAX ( Dates[Quarter] )
                && Dates[year] = MAX ( Dates[year] )
        )
    )


    Create a measure QTD Days as below:

    YTD Days =
    COUNTROWS (
        FILTER (
            ALL ( Dates ),
            Dates[Date] <= MAX ( Dates[Date] )
                && Dates[Quarter] = MAX ( Dates[Quarter] )
                && Dates[year] = MAX ( Dates[year] )
        )
    )

    In the end, create QTD AVG Sales as below:

    QTD AVG Sales =
    DIVIDE ( [QTD Sales], [QTD Days] )

    Please refer to a similar thread you posted before:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5d12711b-27a2-4360-96fb-4df23090c334/calculating-ytd-average-balances-using-exsiting-column-mtd-average-balance?forum=sqlkjpowerpivotforexcel#91b978f0-8461-48b7-b198-be47dd766913


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 8, 2018 3:14 AM
  • Thank you, Wilson. I will give it a try.

    Peace,

    Jeannette


    Wednesday, May 9, 2018 4:29 PM