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 am exploring the use of variables to improve my calculations.
I have two tables
The first one is structured like below. This example is just for one projectskey but there are many.
projectskey | fiscalPeriod | JTDBudgetRevenueAmt | MTDRevenue | JTDRevenue | JTDBacklogAmt | % Month Complete | **bleep** % Month Complete | Time Bucket | Key | Project's Distribution |
1169970 | 202001 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.0625 | 0 - 9% | 1169970|202001 | Flat |
1169970 | 202002 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.125 | 10 - 19% | 1169970|202002 | Flat |
1169970 | 202003 | 5307 | 0 | 0 | 5307 | 0.0625 | 0.1875 | 10 - 19% | 1169970|202003 | Flat |
1169970 | 202004 | 5307 | 2660.37 | 2660.37 | 2646.63 | 0.0625 | 0.25 | 20 - 29% | 1169970|202004 | Flat |
1169970 | 202005 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.3125 | 30 - 39% | 1169970|202005 | Flat |
1169970 | 202006 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.375 | 30 - 39% | 1169970|202006 | Flat |
1169970 | 202007 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.4375 | 40 - 49% | 1169970|202007 | Flat |
1169970 | 202008 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.5 | 50 - 59% | 1169970|202008 | Flat |
1169970 | 202009 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.5625 | 50 - 59% | 1169970|202009 | Flat |
1169970 | 202010 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.625 | 60 - 69% | 1169970|202010 | Flat |
1169970 | 202011 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.6875 | 60 - 69% | 1169970|202011 | Flat |
1169970 | 202012 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.75 | 70 - 79% | 1169970|202012 | Flat |
1169970 | 202101 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.8125 | 80 - 89% | 1169970|202101 | Flat |
1169970 | 202102 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.875 | 80 - 89% | 1169970|202102 | Flat |
1169970 | 202103 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 0.9375 | 90 - 100% | 1169970|202103 | Flat |
1169970 | 202104 | 5307 | 0 | 2660.37 | 2646.63 | 0.0625 | 1 | 90 - 100% | 1169970|202104 | Flat |
So, if we look at the first line, the distribution is flat and the time bucket is 0-9%.
So I would like to use those two values, maybe create two variables, in order to select two values from the table below to perform some calculation.
DistributionPercentageTime BucketTotal Forecast Helper
Flat | 10% | 0 - 9% | 390% |
Flat | 10% | 10 - 19% | 390% |
Flat | 10% | 20 - 29% | 390% |
Flat | 10% | 30 - 39% | 390% |
Flat | 10% | 40 - 49% | 390% |
Flat | 10% | 50 - 59% | 390% |
Flat | 10% | 60 - 69% | 390% |
Flat | 10% | 70 - 79% | 390% |
Flat | 10% | 80 - 89% | 390% |
Flat | 10% | 90 - 100% | 390% |
Front-Loaded | 10% | 0 - 9% | 219% |
Front-Loaded | 28% | 10 - 19% | 219% |
Front-Loaded | 27% | 20 - 29% | 219% |
Front-Loaded | 10% | 30 - 39% | 219% |
Front-Loaded | 5% | 40 - 49% | 219% |
Front-Loaded | 5% | 50 - 59% | 219% |
Front-Loaded | 5% | 60 - 69% | 219% |
Front-Loaded | 5% | 70 - 79% | 219% |
Front-Loaded | 3% | 80 - 89% | 219% |
Front-Loaded | 2% | 90 - 100% | 219% |
Back-Loaded | 2% | 0 - 9% | 480% |
Back-Loaded | 3% | 10 - 19% | 480% |
Back-Loaded | 5% | 20 - 29% | 480% |
Back-Loaded | 5% | 30 - 39% | 480% |
Back-Loaded | 5% | 40 - 49% | 480% |
Back-Loaded | 5% | 50 - 59% | 480% |
Back-Loaded | 10% | 60 - 69% | 480% |
Back-Loaded | 27% | 70 - 79% | 480% |
Back-Loaded | 28% | 80 - 89% | 480% |
Back-Loaded | 10% | 90 - 100% | 480% |
Bell | 2% | 0 - 9% | 475% |
Bell | 4% | 10 - 19% | 475% |
Bell | 8% | 20 - 29% | 475% |
Bell | 15% | 30 - 39% | 475% |
Bell | 22% | 40 - 49% | 475% |
Bell | 22% | 50 - 59% | 475% |
Bell | 15% | 60 - 69% | 475% |
Bell | 8% | 70 - 79% | 475% |
Bell | 4% | 80 - 89% | 475% |
Bell | 2% | 90 - 100% | 475% |
So I would like to create 2 variables, one which is the percentage and the other one will be the forecasthelper
So the value of the percentage variable will be 10% and the forecasthelper will be 390%.
Does someone know how to do that?
Regards,
alain
Solved! Go to Solution.
You just need to add two calculated columns to your Projects table
Percentage =
LOOKUPVALUE (
Distribution[Percentage],
Distribution[Distribution], Projects[Project's Distribution],
Distribution[Time Bucket], Projects[Time Bucket]
)
Forecast =
LOOKUPVALUE (
Distribution[Total Forecast Helper],
Distribution[Distribution], Projects[Project's Distribution],
Distribution[Time Bucket], Projects[Time Bucket]
)
@Anonymous
You could try TREATAS to create the virtual relationship.
Forecast Helper =
CALCULATE (
SELECTEDVALUE ( Rates[Total Forecast Helper] ),
TREATAS (
SELECTCOLUMNS (
Projects,
"Distribution", Projects[Project's Distribution],
"Time Bucket", Projects[Time Bucket]
),
Rates[Distribution],
Rates[Time Bucket]
)
)
I called your first table 'Projects' and your second table 'Rates'.
You would probably be better off creating a key column on both tables that combines the Distribution and Time Bucket so you could just join them but I have attached my sample file for you to look at. I also added a 2nd project with a Front-Loaded distribution to test the measures.
Hello, I have simplified the project table because I am not sure you see the usefull information.
projectskey | fiscalPeriod | Time Bucket | Key | Project's Distribution |
1169970 | 202001 | 0 - 9% | 1169970|202001 | Flat |
1169970 | 202002 | 10 - 19% | 1169970|202002 | Flat |
1169970 | 202003 | 10 - 19% | 1169970|202003 | Flat |
1169970 | 202004 | 20 - 29% | 1169970|202004 | Flat |
1169970 | 202005 | 30 - 39% | 1169970|202005 | Flat |
1169970 | 202006 | 30 - 39% | 1169970|202006 | Flat |
1169970 | 202007 | 40 - 49% | 1169970|202007 | Flat |
1169970 | 202008 | 50 - 59% | 1169970|202008 | Flat |
1169970 | 202009 | 50 - 59% | 1169970|202009 | Flat |
1169970 | 202010 | 60 - 69% | 1169970|202010 | Flat |
1169970 | 202011 | 60 - 69% | 1169970|202011 | Flat |
1169970 | 202012 | 70 - 79% | 1169970|202012 | Flat |
1169970 | 202101 | 80 - 89% | 1169970|202101 | Flat |
1169970 | 202102 | 80 - 89% | 1169970|202102 | Flat |
1169970 | 202103 | 90 - 100% | 1169970|202103 | Flat |
1169970 | 202104 | 90 - 100% | 1169970|202104 | Flat |
So the Timebucket is know as well the distribution. So my question was is there a nice way to create 2 variables, forecasthelper and percentage and use those to perform some calculation.
Here's a sample of my projects table
projectskey | fiscalPeriod | Time Bucket | Key | Project's Distribution |
1169970 | 202001 | 0 - 9% | 1169970|202001 | Front-Loaded |
1169970 | 202002 | 10 - 19% | 1169970|202002 | Front-Loaded |
1169970 | 202003 | 10 - 19% | 1169970|202003 | Front-Loaded |
1169970 | 202004 | 20 - 29% | 1169970|202004 | Front-Loaded |
1169970 | 202005 | 30 - 39% | 1169970|202005 | Front-Loaded |
1169970 | 202006 | 30 - 39% | 1169970|202006 | Front-Loaded |
1169970 | 202007 | 40 - 49% | 1169970|202007 | Front-Loaded |
1169970 | 202008 | 50 - 59% | 1169970|202008 | Front-Loaded |
1169970 | 202009 | 50 - 59% | 1169970|202009 | Front-Loaded |
1169970 | 202010 | 60 - 69% | 1169970|202010 | Front-Loaded |
1169970 | 202011 | 60 - 69% | 1169970|202011 | Front-Loaded |
1169970 | 202012 | 70 - 79% | 1169970|202012 | Front-Loaded |
1169970 | 202101 | 80 - 89% | 1169970|202101 | Front-Loaded |
1169970 | 202102 | 80 - 89% | 1169970|202102 | Front-Loaded |
1169970 | 202103 | 90 - 100% | 1169970|202103 | Front-Loaded |
1169970 | 202104 | 90 - 100% | 1169970|202104 | Front-Loaded |
Here's my distribution Table
DistributionPercentageTime BucketTotal Forecast Helper
Flat | 0.10 | 0 - 9% | 3.90 |
Flat | 0.10 | 10 - 19% | 3.90 |
Flat | 0.10 | 20 - 29% | 3.90 |
Flat | 0.10 | 30 - 39% | 3.90 |
Flat | 0.10 | 40 - 49% | 3.90 |
Flat | 0.10 | 50 - 59% | 3.90 |
Flat | 0.10 | 60 - 69% | 3.90 |
Flat | 0.10 | 70 - 79% | 3.90 |
Flat | 0.10 | 80 - 89% | 3.90 |
Flat | 0.10 | 90 - 100% | 3.90 |
Front-Loaded | 0.10 | 0 - 9% | 2.19 |
Front-Loaded | 0.28 | 10 - 19% | 2.19 |
Front-Loaded | 0.27 | 20 - 29% | 2.19 |
Front-Loaded | 0.10 | 30 - 39% | 2.19 |
Front-Loaded | 0.05 | 40 - 49% | 2.19 |
Front-Loaded | 0.05 | 50 - 59% | 2.19 |
Front-Loaded | 0.05 | 60 - 69% | 2.19 |
Front-Loaded | 0.05 | 70 - 79% | 2.19 |
Front-Loaded | 0.03 | 80 - 89% | 2.19 |
Front-Loaded | 0.02 | 90 - 100% | 2.19 |
Back-Loaded | 0.02 | 0 - 9% | 4.80 |
Back-Loaded | 0.03 | 10 - 19% | 4.80 |
Back-Loaded | 0.05 | 20 - 29% | 4.80 |
Back-Loaded | 0.05 | 30 - 39% | 4.80 |
Back-Loaded | 0.05 | 40 - 49% | 4.80 |
Back-Loaded | 0.05 | 50 - 59% | 4.80 |
Back-Loaded | 0.10 | 60 - 69% | 4.80 |
Back-Loaded | 0.27 | 70 - 79% | 4.80 |
Back-Loaded | 0.28 | 80 - 89% | 4.80 |
Back-Loaded | 0.10 | 90 - 100% | 4.80 |
Bell | 0.02 | 0 - 9% | 4.75 |
Bell | 0.04 | 10 - 19% | 4.75 |
Bell | 0.08 | 20 - 29% | 4.75 |
Bell | 0.15 | 30 - 39% | 4.75 |
Bell | 0.22 | 40 - 49% | 4.75 |
Bell | 0.22 | 50 - 59% | 4.75 |
Bell | 0.15 | 60 - 69% | 4.75 |
Bell | 0.08 | 70 - 79% | 4.75 |
Bell | 0.04 | 80 - 89% | 4.75 |
Bell | 0.02 | 90 - 100% | 4.75 |
So, I would like to find a way to add the percentage column and the forecasthelper column to the first table so at the end we obtain,
projectskey | fiscalPeriod | Time Bucket | Key | Project's Distribution | Percentage | ForecastHelper |
1169970 | 202001 | 0 - 9% | 1169970|202001 | Front-Loaded | 0.1 | 2.19 |
1169970 | 202002 | 10 - 19% | 1169970|202002 | Front-Loaded | 0.28 | 2.19 |
1169970 | 202003 | 10 - 19% | 1169970|202003 | Front-Loaded | 0.28 | 2.19 |
1169970 | 202004 | 20 - 29% | 1169970|202004 | Front-Loaded | 0.27 | 2.19 |
1169970 | 202005 | 30 - 39% | 1169970|202005 | Front-Loaded | 0.1 | 2.19 |
1169970 | 202006 | 30 - 39% | 1169970|202006 | Front-Loaded | 0.1 | 2.19 |
1169970 | 202007 | 40 - 49% | 1169970|202007 | Front-Loaded | 0.05 | 2.19 |
1169970 | 202008 | 50 - 59% | 1169970|202008 | Front-Loaded | 0.05 | 2.19 |
1169970 | 202009 | 50 - 59% | 1169970|202009 | Front-Loaded | 0.05 | 2.19 |
1169970 | 202010 | 60 - 69% | 1169970|202010 | Front-Loaded | 0.05 | 2.19 |
1169970 | 202011 | 60 - 69% | 1169970|202011 | Front-Loaded | 0.05 | 2.19 |
1169970 | 202012 | 70 - 79% | 1169970|202012 | Front-Loaded | 0.05 | 2.19 |
1169970 | 202101 | 80 - 89% | 1169970|202101 | Front-Loaded | 0.03 | 2.19 |
1169970 | 202102 | 80 - 89% | 1169970|202102 | Front-Loaded | 0.03 | 2.19 |
1169970 | 202103 | 90 - 100% | 1169970|202103 | Front-Loaded | 0.02 | 2.19 |
1169970 | 202104 | 90 - 100% | 1169970|202104 | Front-Loaded | 0.02 | 2.19 |
You just need to add two calculated columns to your Projects table
Percentage =
LOOKUPVALUE (
Distribution[Percentage],
Distribution[Distribution], Projects[Project's Distribution],
Distribution[Time Bucket], Projects[Time Bucket]
)
Forecast =
LOOKUPVALUE (
Distribution[Total Forecast Helper],
Distribution[Distribution], Projects[Project's Distribution],
Distribution[Time Bucket], Projects[Time Bucket]
)
I have test your solution and it works. But is it possible to do the same using variable to perform some calculation, in the case that we wish to just add the calculation result not all the columns used in the calculation.
What calculation are you wanting to do?
https://1drv.ms/x/s!At4hIgQFrUxYjVP2Y5r8v88f4cSk?e=kP6jIP
You can have access to the Excel file using the above link.
It will give you an idea of what I would like to do. Two things that has change since the first request is that now the distribution (flat, back-loaded, front-loaded, bell) is already define using specific criteria such as business line and budget range.
Also, instead of calculating every thing on a daily basis, now it is on a monthly basis.
Finally, I am trying to use variables to perform the calculation of let's say the last 6 columns while I need to present only two of those.
Please let me know if it is possible to do that using variable otherwise, I will continue using the calculated column approach
Hi @Anonymous ,
Sorry, I can't open the link you shared. Could you please upload it to OneDrive for Business, then share the link with us?
Share OneDrive files and folders
Best Regards
Rena
@Anonymous , not very clear to me, See if segmentation or bucketing can help
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |