Matrix - Percent change between columns

[Anonymous]

Hello,

I'm not sure if what I'm trying to do is possible. I have a matrix visualization comparing product growth YTD vs. Prior YTD. I want to show the percentage difference in a separate column. I want it to look like below:

Product	2018 YTD	2019 YTD	% Difference
Product1	5	8	60%
Product2	10	8	(20%)

How do I accomplish this?

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

First create a new table as below:

Then create 2 measures as below:

Measure 2 = 
var c=SELECTEDVALUE('Table'[2018 YTD])
var d=SELECTEDVALUE('Table'[2019 YTD])
Return
 IF(d>c,FORMAT(DIVIDE(d-c,c),"percent"),CONCATENATE("("&FORMAT(DIVIDE(c-d,c),"percent"),")"))

Measure = 
var a =SELECTEDVALUE('Table'[Product])
var b=SELECTEDVALUE('Table (2)'[Column1])
Return
SWITCH(TRUE(),a="Product1"&&b="2018YTD","5",a="Product1"&&b="2019YTD","8",a="Product1"&&b="% Difference",'Table (2)'[Measure 2],a="Product2"&&b="2018YTD","10",a="Product2"&&b="2019YTD","8",a="Product2"&&b="% Difference",'Table (2)'[Measure 2])

Finally you will see :

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

[Anonymous]

Hi, I figured out a solution, using some of your response. In the initial table with more detail, I added a measure:

Percent Diff = var c = CALCULATE(
    SUMX(GrowthDetail,
    GrowthDetail[Quantity]),
    FILTER(GrowthDetail,
        GrowthDetail[Revenue Year]=GrowthDetail[Current Year]
        && GrowthDetail[Billing Effective Date].[Date] <= GrowthDetail[Current YTD].[Date]))
var p = CALCULATE(
    SUMX(GrowthDetail,
    GrowthDetail[Quantity]),
    FILTER(GrowthDetail,
        GrowthDetail[Revenue Year]=GrowthDetail[Current Year]-1
        && GrowthDetail[Billing Effective Date].[Date] <= GrowthDetail[Prior YTD].[Date]))
  Return
  IF(p=0,IF(c=0,0,1),c/p-1)    

[Anonymous]

Hi, thanks for the response. In your example, is it dynamic for drilldowns? I have a slicer. If I select to just see North America, will it automatically adjust?

[v-kelly-msft]

Hi @Anonymous ,

Measure is dynamic calculation,it can be changed by your selection.So have you worked out your problem?

 
Best Regards,
Kelly

[Anonymous]

Yes, I solved it. I'm new to BI and hadn't yet used measures. Your response put my on the right path. Thanks!

[amitchandak]

In this case, you need to have YTD, prior YTD and change % as columns. You can use Time Intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Change % = ([YTD Sales]/[Last YTD Sales])-1

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

Hi, thanks for the help. I am not exactly doing YTD, but as of prior month end. So I adjusted to use DATESBETWEEN instead of DATESYTD. Below is my formula:

YTD_Quantity = CALCULATE(SUM(GrowthDetail[Quantity]),DATESBETWEEN(GrowthDetail[Billing Effective Date].[Date],GrowthDetail[Begin_Cur_Year],GrowthDetail[Cur_YTD]))

This works just fine. However, when I try to do the same thing for prior year, I get a circular reference. Below is the formula:

Prior_YTD_Quantity = CALCULATE(SUM(GrowthDetail[Quantity]),DATESBETWEEN(GrowthDetail[Billing Effective Date].[Date],GrowthDetail[Begin_Prior_Year],GrowthDetail[Prior_YTD]))

Even when I simply copy the first formula and change the column name, I receive the same error. It reads: "A circular dependency was detected: GrowthDetail[Prior_YTD_Quantity],GrowthDetail[YTD_Quantity],GrowthDetail[Prior_YTD_Quantity]."