Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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)
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?
Hi @Anonymous ,
Measure is dynamic calculation,it can be changed by your selection.So have you worked out your problem?
Best Regards,
Kelly
Yes, I solved it. I'm new to BI and hadn't yet used measures. Your response put my on the right path. Thanks!
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
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]."
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |