Use calculated values in another calculation?

[Tony1987]

I've got an issue where I want to show dynamic values based off of date hierarchy level. I've got most of it down, but I've got one last issue with the secondary Y axis calculation. Here's what's going on.

This is a clustered column with secondary Y-axis line chart. I was able to generate dynamic clustered column data based on date hierarchy that weights the values based on volume from the lower hierarchy periods.

Total Weighted $/BBL =

VAR __CATEGORY_VALUES = VALUES('Date'[Date])

RETURN

    DIVIDE(

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE(

                SUM('PowerBI Tab'[Total Location Uplift])

            )

        ),

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE(SUM('PowerBI Tab'[Product Volume]))

        )

    )

This is all working properly. Next, I wanted to have dynamic period over period % change that would also be based on the date hierarchy level. I am using the coding here.

$/bbl % Change =

VAR __PREV_YEAR = CALCULATE(SUM('PowerBI Tab'[ Total $/BBL]), DATEADD('Date'[Date], -1, YEAR))

VAR __YOY = DIVIDE(SUM('PowerBI Tab'[ Total $/BBL]) - __PREV_YEAR, ABS(__PREV_YEAR))

VAR __PREV_QUARTER = CALCULATE(SUM('PowerBI Tab'[ Total $/BBL]), DATEADD('Date'[Date], -1, QUARTER))

VAR __QOQ = DIVIDE(SUM('PowerBI Tab'[ Total $/BBL]) - __PREV_QUARTER, ABS(__PREV_QUARTER))

VAR __PREV_MONTH = CALCULATE(SUM('PowerBI Tab'[ Total $/BBL]), DATEADD('Date'[Date], -1, MONTH))

VAR __MOM = DIVIDE(SUM('PowerBI Tab'[ Total $/BBL]) - __PREV_MONTH, ABS(__PREV_MONTH))

RETURN

    SWITCH(TRUE(),

        HASONEFILTER('Date'[Date].[Month]), __MOM,

        HASONEFILTER('Date'[Date].[Quarter]), __QOQ,

        HASONEFILTER('Date'[Date].[Year]), __YOY,

        BLANK()

    )

This almost works the way I want, however the problem is that when I move from months to quarters or from quarters to years, it uses a simple average of the $/BBL values instead of a weighted average like how they are displayed on the column portion. I tried replacing "Total $/BBL" in the 2nd equation with my calculated "Total Weighted $/BBL", but DAX wouldn't allow me to do that. I have also tried combining the DAX expressions and merging the two within a single expression but couldn't get that to work either?

Any help would be greatly appreciated.

[v-kaiyue-msft]

Hi @Tony1987 ,

You could try using the ALLSELECTED function when calculating the bbl sum, which gets the context representing all rows and columns in the query while preserving context beyond explicit filters and row and column filters. Something like this.

CALCULATE(SUM('PowerBI Tab'[Total $/BBL]),
FILTER(ALLSELECTED('financials'),
'Date'[Date]>=DATE(YEAR(MAX('Date'[Date]))-1,MONTH(MAX('Date'[Date])),DAY(MAX('Date'[Date])) )&&
'Date'[Date]<=MAX('Date'[Date])))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.