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 ,

 

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.