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.

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Miguel,

 

Below is the link to the new file that uses the previous screenshot:

 

https://drive.google.com/file/d/1MCzeCEaGFjLriaQExEdD4TV_0RGH5J7d/view?usp=sharing

 

I REALLY appreciate your help with this as this report as been ongoing for a year. So you're right, I think I have the corrrect calculations to get the current average price, current volume, and previous average price.

 

My issue is getting the variance calculation which is

(Current Average Price - Previous Average Price) * Current Volume

 

-- But only for rows where the license key are the same. So in the table below, I only need the rows where the keys match. Blue vs Red

 

 

Here is an Excel file with the correct result in green:

 

https://drive.google.com/file/d/1y4f0n2O4SLsKdMM-H5hi65gXT-0xKvAF/view?usp=sharing

 

You have no idea how much your help means to me at this stage. Im ready to not be up all night trying different things.

 

MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous ,

 

This is a question about context, if you take out the date from your setup and add the following measure:

 

Variance = ([Current Avg Price] - [Previous Avg Price]) * [Current Volume]

 

You get the result below:

Has you can see the value highlited is according to your excel file, the question on this case is the total valuesif you want then calculated in the same way or has a SUM of all the previous values.

 

Check PBIX file attach and get back to me to what is correct or incorrect in the calculation.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    The calculation is correct but I need the date included because they are trying to get a bar graph with 4 other variables (that customer start & stop thing mentioned before). So in this ecample, while that 5.57 is correct, the other rows should be 0 since they didnt have a matching value. This is where I was trying to use Count Rows maybe but I couldnt figure out how to do it correctly:

     

     

    • MFelix's avatar
      MFelix
      Super User
      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.