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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CKG
New Member

How to Calculate Variances on Dimensions in Columns or Rows

Hi, I'm a new Power BI user, with a question I have tried to resolve by referring to the forum questions.  Cannot find the same scenario, so hoping someone can help.

I have created a matrix table to report Trade Spend by expense bucket, and for the time dimensions of Month, Last Year Month, and Forecast Month:

 

 Matrix.JPG

 

However, I cannot find a way to calculate the variance between Month and Last Year or Month and Forecast.  I have also tried a different configuration of the expense buckets in Columns and the Time Dimension in Rows.  This of course makes no difference, because of the way the data has been extracted from SAP BW.  In BW I had 2x header rows, one for Value Field and one for Time Dimension.  One alternative was to merge the Value Field with the Time Dimension as a header row.  Then it is easy to calculate the variances.  The problem with that is then you cannot display the data neatly in a table (or at least I don't know how).  Each time dimension and expense bucket is separate, so you cannot line up the month values as I have above.  I'm not sure I have expressed the problem clearly enough. In the end I brought the Time Dimension in as a separate field. My issue is that I don't know how to reference a single value in the 'Dimension' field of my table, for the DAX formulas:

 

 Dimension.JPG

 

Is anyone able to advise?

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@CKG,

 

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Students-variations-year-over-year/m-p/345749#M155124

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi. Thank you for your reply.  Unfortunately, I do not see how to adapt that solution to my situation.  I have tried to modify your formulas to my case, however, I continue to receive syntax errors.

 

Is someone able to provide the formulas specific to my example?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.