PowerBI Olap Cube Variance Column Matrix

[Anonymous]

Hi everyone,

Very new to PowerBI. I know I could do this all in excel in a second and schedule refreshes via powerautomate however I want the challenge of building powerBI dashboards. I want to use a matrix table to show actual and budget, that much is easy, however I want a third column showing variance between these two. My data connection is an OLAP cube. This is my current set-up for the matrix visual:

 

and this is my scenario table:

I thought I could do a new measure and do a sum is actual - sum is bud but it won't display my visual. Can anyone provide some help or a good resource for me to research?

[DataInsights]

@Anonymous,

Try this measure. Alternatively, you could create base measures (one for Actual, and one for Budget), and use them in the Variance measure.

Variance =
VAR vActual =
    CALCULATE ( [Amt $], Scenario[Scenario Name] = "Actual" )
VAR vBudget =
    CALCULATE ( [Amt $], Scenario[Scenario Name] = "Budget" )
VAR vResult = vActual - vBudget
RETURN
    vResult

[Anonymous]

Hi there,

Apologies for the delay, i was on vacation and thank you for taking the time to get back to me. I have successfully created the measure, however when I add it into my visual this is the result:

I would like 1 column for the account product, one for the Actual Amt $ and one for the budget amt $ and then the variance column at the end. Any advice?

[DataInsights]

@Anonymous,

You may need to use MDX according to the article below.

https://learn.microsoft.com/en-us/system-center/scsm/olap-cubes-overview?view=sc-sm-2022 

"After the raw data has been processed in an OLAP cube, users can perform more complex calculations and queries using multidimensional expressions (MDX) to define their own measure expressions or calculated members."