cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thomaskelly Regular Visitor
Regular Visitor

Create a Calculated Column in a matrix

Hello,

 

I have connected power Bi to an azzure db and have been reasonably successful so far in compiling a dashboard with numerous graphs and tables. However I have now reached a hurdle that has me totally stumped and would welcome some assistance.

 

I have 3 years of data in a table related to sales invoices all with a invoice date column and an invoice amount column as well as other information. I have  created a matrix in power Bi that can be filtered  by way of a slicer to ( for example) the month of august 2016 and august 2015 to compare product category sales for those filtered months. What I want is to be able to add an additional column beside these two that recalculates when the matrix is filtered to other months for example January 15 vs January 16.  I want this column to calculate the % variance between the rows of data in the matrix which is related to product category sales.

 

Can anyone help? Is there a way of doing this ?

 

Any thoughts would be most appreciated

 

Many Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
shona Frequent Visitor
Frequent Visitor

Re: Create a Calculated Column in a matrix

I am not really sure if there is an easier way. I have two date tables which I use as slicers allowing me to select two periods to compare and then I have a master date table where I create my calculations, e.g. sales for period 1 and sales for period 2

 

Hope this makes sense and apologies if I have misunderstood what you are trying to do.

 

Edit: I have attached a screenshot of how my matrix looks if that helps.Capture.PNG


Shona

 

 

thomaskelly Regular Visitor
Regular Visitor

Re: Create a Calculated Column in a matrix

Hi Shona

 

Thanks for that Smiley Happy

 

I  have managed to do it by creating a seperate date table then by creating a previous year measure and % change measure. When I now select my required month by way of a filter it will show this month for the current year and the previous year coloum will filter to show the sales for that period last year and the % variance between these two months.

 

Thanks for your help...

 

 

10 REPLIES 10
BhaveshPatel Super Contributor
Super Contributor

Re: Create a Calculated Column in a matrix

Hi Thomas,

 

It would be easy for us to give you a solution if you will share snapshot of data or sample file.

 

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
thomaskelly Regular Visitor
Regular Visitor

Re: Create a Calculated Column in a matrix

 

screen shot.pngI need an extra column besdide the two months that calculates the variance

shona Frequent Visitor
Frequent Visitor

Re: Create a Calculated Column in a matrix

I have done something like this before by creating two date tables for your filtered dates (with no relationship set) and then using filter and crossjoin to get the filtered dates.

 

Example create following measure for sales in each of your selected date ranges:

 CALCULATE(SUM(.....),

FILTER (
CROSSJOIN ( VALUES ( SalesCalendar[date] ), VALUES ( 'period 1'[date] ) ),
SalesCalendar[date] = 'period 1'[date]
))

 

SalesCalendar is my master date table and then I have another two tables for the filtered dates period1 and period2. You can repeat above measure for your second date range and then create a measure to do the difference between the two.

 

I am no DAX expert so there may be an easier way of doing this or I may have completely misunderstood Smiley Happy

 

Shona

thomaskelly Regular Visitor
Regular Visitor

Re: Create a Calculated Column in a matrix

Hi Shona

 

Thanks for the reply .

 

I want to be able to use the filter/slicer , so would I then have to create a measure for every month , year and quarter in my data along with similar date tables?

 

I hope there is an easier way Smiley Happy

 

Kind Regards

shona Frequent Visitor
Frequent Visitor

Re: Create a Calculated Column in a matrix

I am not really sure if there is an easier way. I have two date tables which I use as slicers allowing me to select two periods to compare and then I have a master date table where I create my calculations, e.g. sales for period 1 and sales for period 2

 

Hope this makes sense and apologies if I have misunderstood what you are trying to do.

 

Edit: I have attached a screenshot of how my matrix looks if that helps.Capture.PNG


Shona

 

 

thomaskelly Regular Visitor
Regular Visitor

Re: Create a Calculated Column in a matrix

Hi Shona

 

Thanks for that Smiley Happy

 

I  have managed to do it by creating a seperate date table then by creating a previous year measure and % change measure. When I now select my required month by way of a filter it will show this month for the current year and the previous year coloum will filter to show the sales for that period last year and the % variance between these two months.

 

Thanks for your help...

 

 

afernando Regular Visitor
Regular Visitor

Re: Create a Calculated Column in a matrix

Hi @shona and @thomaskelly

 

I'm also trying to achieve the same to compare FY 16 and FY 17 sales and quantity. sales Qty.JPG

 

I'm struggling with creating columns to calculate the differences between 2 periods. Appreciated if you could explain how to achieved this. Thanks!

 

Anton

madhushree Regular Visitor
Regular Visitor

Re: Create a Calculated Column in a matrix

Hi , We are building a report in power bi . This is a matrix, where column is YYYY-MM. It also has 2 other columns 12M and 3M. Here 3M = ((Current month – Prev 3 Month)/prev 4 month)*100 For example – 3M = (column (2016-07)- column (2016-04)) / column (2016-03)*100 And 12M = ((Current Month – Prev 12 Month)/prev 13 month)*100 For example – 12M = (column (2016-07)- column (2015-07)) / column (2015-06)*100 We are not able to do the above calculations, Can you please us suggest how can this be done. Thanks and Regards, Madhushree.K.Capture.PNG

purana2002 Regular Visitor
Regular Visitor

Re: Create a Calculated Column in a matrix

How did you create this report? WHich visualisation , can you guide me thru?

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 230 members 3,005 guests
Please welcome our newest community members: