Need some help in getting variance by selected quarters dynamically coming out of excel data

[VarmaPowerBI]

Hi Everyone,

I am new to power BI, and I have a requirement, where I need to show dynamically, variance between any quarter that they select in power bi report.

Data is coming out of excel file, that our management manually enters the data every quarter.

We used, matrix usual to display the values by quarter and for respective row value.

To get the columns in sorted orders we used an manual table for custom order in matrix visual.

But now, our management wants to get the variance between any 2 columns that they select.

In other words, they wanted to see q1 vs q2 or q1 vs q3 (actual vs budget). And they wanted to do it for any quarter. 

Sample Files 

I am attaching the sample data file and pbix file that i have. any help or directions in this matter is great.

Their vision, is lets say if they select q1 and q2 then they wanted to see those values along with variance on the same visual.

Thanks in advance, for going through my post.

Thanks,

Varma

[v-lili6-msft]

HI @VarmaPowerBI 

You could try use this formula to create a measure for the variance between.

Measure = VAR _LASTQ=CALCULATE(MAX('Column order'[Quarter - Year E]),FILTER(ALLSELECTED('Column order'),'Column order'[Quarter - Year E]<MAX('Column order'[Quarter - Year E])&&'Column order'[Budget - Actual]=MAX('Column order'[Budget - Actual]))) RETURN
CALCULATE(SUM(Sheet1[Value]))-CALCULATE(SUM(Sheet1[Value]),FILTER(ALLSELECTED('Column order'),'Column order'[Quarter - Year E]=_LASTQ))

Regards,

Lin

[VarmaPowerBI]

@v-lili6-msft  Thanks for the Measure formula, it is actually getting me the variance of the quarters in selection, But in my matrix visual if i use that measure its getting repeated for each quarter. including the first quarter in selection, Can I not have the variance for the first quarter to be displayed.

So in this case variance is next to both Q1, Q2 and Q3. Can we have this only for Q2 and Q3 but not for Q1.

Despite all this, I sincerely thank you for the measure calculation you provided to me. It helped me alot

Thanks,

Varma

[v-lili6-msft]

hi @VarmaPowerBI 

It could not achieve in power bi for now.

For your requirement, you could add a condotional that if lastquarter for current quarter is blank, then rerurn blank, for example:

Measure =
VAR _LASTQ =
    CALCULATE (
        MAX ( 'Column order'[Quarter - Year E] ),
        FILTER (
            ALLSELECTED ( 'Column order' ),
            'Column order'[Quarter - Year E] < MAX ( 'Column order'[Quarter - Year E] )
                && 'Column order'[Budget - Actual] = MAX ( 'Column order'[Budget - Actual] )
        )
    )
RETURN
    IF (
        CALCULATE (
            SUM ( Sheet1[Value] ),
            FILTER (
                ALLSELECTED ( 'Column order' ),
                'Column order'[Quarter - Year E] = _LASTQ
            )
        )
            = BLANK (),
        BLANK (),
        CALCULATE ( SUM ( Sheet1[Value] ) )
            - CALCULATE (
                SUM ( Sheet1[Value] ),
                FILTER (
                    ALLSELECTED ( 'Column order' ),
                    'Column order'[Quarter - Year E] = _LASTQ
                )
            )
    )

Regards,

Lin