Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

URGENT: Variance Report

I need help figuring out this calculation in Power BI. It's easy in Tableau with date parameters, but not sure how to do it in Power BI.   The calculation rules are below:   Customer Start and S...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    The question here is regarding the period selected in the Current Period and the granularity.

     

    What I did as to make changes to your two measures:

    Previous Avg Price =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Invoice Variance',
            'Invoice Variance'[item],
            "CurAvgPrice",
                DIVIDE (
                    SUM ( 'Invoice Variance'[ext_price] ),
                    SUM ( 'Invoice Variance'[volume] ),
                    0
                )
        ),
        [CurAvgPrice]
    ),
    FILTER (
        ALL ( 'Invoice Variance'[inv_date] ),
        'Invoice Variance'[inv_date]
            <= MAX ( 'Current'[Date] ) - 365
            && 'Invoice Variance'[inv_date]
                >= MIN ( 'Current'[Date] ) - 365
    )
)



Current Avg Price =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Invoice Variance',
            'Invoice Variance'[item],
            "CurAvgPrice",
                DIVIDE (
                    SUM ( 'Invoice Variance'[ext_price] ),
                    SUM ( 'Invoice Variance'[volume] ),
                    0
                )
        ),
        [CurAvgPrice]
    ),
    FILTER (
        ALL ( 'Invoice Variance'[inv_date] ),
        'Invoice Variance'[inv_date] <= MAX ( 'Current'[Date] )
            && 'Invoice Variance'[inv_date] >= MIN ( 'Current'[Date] )
    )
)

     

    Has you can see below the lines that you refer are picking up the values:

     

    All others have only a single value previous or current. This is impacted by a slicer with the dates of current with 2021 dates.

     

    What is happening is that since you were using the full date table when you compare the current date 2020 with previous year 2019 you are not getting values because your data starts in 2020, on the other and for the current values when you search for the current value on 2021 you are not getting values because your data is of 2020.. (not sure if I made it clear)

     

    Check PBIX file attach.

     

     

     

     

MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous ,

 

To what I can see from your model the two tabvle are exactly the same only difference are the last 3 columns that you calculated in dax.

 

Believe that you can make the calculations based on a single table that then is filtered by the parameter dates that you created.

 

Another question is about the previous period, you want to select that period from the slicer also, or is it the previous 12 months?

 

Also not understanding the question about the two subcategories you want to put the customers in categories?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I can easily replicate the total avg price and variance but the issue is that they need it by ONLY items where the customer ordered the same product in the two time periods. For example, this is an item made in january

     

     

    I have the 4.04 and 3.84 values but the actual number is only for 1198 when it appears in the current date selection and the previous selection which is current -365

     

    I used the calculations to get the main number but do not know how to get the match:

    Current Avg Price =
    var CurLastDate = MAX('Current'[Date])
    var CurFirstDate = MIN('Current'[Date])
    return
    calculate (
        SUMX(
    SUMMARIZE('Invoice Variance','Invoice Variance'[item],
    "CurAvgPrice",DIVIDE(SUM('Invoice Variance'[ext_price]),SUM('Invoice Variance'[volume]),0)),[CurAvgPrice]),
        KEEPFILTERS( 'Invoice Variance'[inv_date] <= CurLastDate && 'Invoice Variance'[inv_date] >= CurFirstDate)
    )
    Current Volume =
    var CurLastDate = MAX('Current'[Date])
    var CurFirstDate = MIN('Current'[Date])
    return
    calculate (
        sum ( 'Invoice Variance'[volume] ),
        KEEPFILTERS( 'Invoice Variance'[inv_date] <= CurLastDate && 'Invoice Variance'[inv_date] >= CurFirstDate)
    )
     
    Previous Avg Price =
    var PrevLastDate = MAX('Current'[Date])-365
    var PrevFirstDate = MIN('Current'[Date])-365
    return
    calculate (
        SUMX(
    SUMMARIZE('Invoice Variance','Invoice Variance'[item],
    "PrevAvgPrice",DIVIDE(SUM('Invoice Variance'[ext_price]),SUM('Invoice Variance'[volume]),0)),[PrevAvgPrice]),
        KEEPFILTERS( 'Invoice Variance'[inv_date] <= PrevLastDate && 'Invoice Variance'[inv_date] >= PrevFirstDate)
    )
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      How would I only get the values where the current key and the previous key match? (e.g. 1189-1)

       

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi Anonymous ,

         

        Sorry but I'm still not getting how you are filtering the information, I'm not abble to filter out the information in the same way you have it and I have a lot more lines than you on the calculations.

         

        The formulas you present on the screen shot are correct to what I can see so what you need to do is a calculation by the item and picking up both of the CURRENT and AVERAGE  prices.

         

        Should be something similar to: 

        SUMX(VALUES(Table[Item], [Current] - [Average])

         

        This would give you the difference between the two values, this is just a guessing and may need some changes since has I said I'm not understanding the filter context of your visualization.

         

        Regarding the second part the COUNTROWS syntax is made over a table so you need to change the way you have your formula:

        MEASURE =
CALCULATE (
    COUNTROWS ( FILTER ( 'Invoice Variance', [Current KEY] = [Previous KEY] ) )
)