Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Tony1987
New Member

Use calculated values in another calculation?

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.

 

Tony1987_0-1713284732490.png

 

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.
1 REPLY 1
v-kaiyue-msft
Community Support
Community Support

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors