Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

how to use variables to improve formulas calculations

‎07-08-2020 09:17 AM

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.

 

projectskeyfiscalPeriodJTDBudgetRevenueAmtMTDRevenueJTDRevenueJTDBacklogAmt% Month Complete**bleep** % Month CompleteTime BucketKeyProject's Distribution
1169970202001530702660.372646.630.06250.06250 - 9%1169970|202001Flat
1169970202002530702660.372646.630.06250.12510 - 19%1169970|202002Flat
116997020200353070053070.06250.187510 - 19%1169970|202003Flat
116997020200453072660.372660.372646.630.06250.2520 - 29%1169970|202004Flat
1169970202005530702660.372646.630.06250.312530 - 39%1169970|202005Flat
1169970202006530702660.372646.630.06250.37530 - 39%1169970|202006Flat
1169970202007530702660.372646.630.06250.437540 - 49%1169970|202007Flat
1169970202008530702660.372646.630.06250.550 - 59%1169970|202008Flat
1169970202009530702660.372646.630.06250.562550 - 59%1169970|202009Flat
1169970202010530702660.372646.630.06250.62560 - 69%1169970|202010Flat
1169970202011530702660.372646.630.06250.687560 - 69%1169970|202011Flat
1169970202012530702660.372646.630.06250.7570 - 79%1169970|202012Flat
1169970202101530702660.372646.630.06250.812580 - 89%1169970|202101Flat
1169970202102530702660.372646.630.06250.87580 - 89%1169970|202102Flat
1169970202103530702660.372646.630.06250.937590 - 100%1169970|202103Flat
1169970202104530702660.372646.630.0625190 - 100%1169970|202104Flat

 

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

Flat10%0 - 9%390%
Flat10%10 - 19%390%
Flat10%20 - 29%390%
Flat10%30 - 39%390%
Flat10%40 - 49%390%
Flat10%50 - 59%390%
Flat10%60 - 69%390%
Flat10%70 - 79%390%
Flat10%80 - 89%390%
Flat10%90 - 100%390%
Front-Loaded10%0 - 9%219%
Front-Loaded28%10 - 19%219%
Front-Loaded27%20 - 29%219%
Front-Loaded10%30 - 39%219%
Front-Loaded5%40 - 49%219%
Front-Loaded5%50 - 59%219%
Front-Loaded5%60 - 69%219%
Front-Loaded5%70 - 79%219%
Front-Loaded3%80 - 89%219%
Front-Loaded2%90 - 100%219%
Back-Loaded2%0 - 9%480%
Back-Loaded3%10 - 19%480%
Back-Loaded5%20 - 29%480%
Back-Loaded5%30 - 39%480%
Back-Loaded5%40 - 49%480%
Back-Loaded5%50 - 59%480%
Back-Loaded10%60 - 69%480%
Back-Loaded27%70 - 79%480%
Back-Loaded28%80 - 89%480%
Back-Loaded10%90 - 100%480%
Bell2%0 - 9%475%
Bell4%10 - 19%475%
Bell8%20 - 29%475%
Bell15%30 - 39%475%
Bell22%40 - 49%475%
Bell22%50 - 59%475%
Bell15%60 - 69%475%
Bell8%70 - 79%475%
Bell4%80 - 89%475%
Bell2%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

 

Labels:
Message 1 of 10
540 Views
0
1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User
In response to Anonymous

‎07-08-2020 11:57 AM

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]
)

 

View solution in original post

Message 6 of 10
494 Views
9 REPLIES 9
jdbuchanan71
Super User
Super User

‎07-08-2020 09:57 AM

@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.

jdbuchanan71_0-1594227448639.png

 

how-to-use-variables-to-improve-formulas-calculations.pbix
Message 3 of 10
529 Views
0
Anonymous
Not applicable
In response to jdbuchanan71

‎07-08-2020 10:10 AM

Hello, I have simplified the project table because I am not sure you see the usefull information.

 

projectskeyfiscalPeriodTime BucketKeyProject's Distribution
11699702020010 - 9%1169970|202001Flat
116997020200210 - 19%1169970|202002Flat
116997020200310 - 19%1169970|202003Flat
116997020200420 - 29%1169970|202004Flat
116997020200530 - 39%1169970|202005Flat
116997020200630 - 39%1169970|202006Flat
116997020200740 - 49%1169970|202007Flat
116997020200850 - 59%1169970|202008Flat
116997020200950 - 59%1169970|202009Flat
116997020201060 - 69%1169970|202010Flat
116997020201160 - 69%1169970|202011Flat
116997020201270 - 79%1169970|202012Flat
116997020210180 - 89%1169970|202101Flat
116997020210280 - 89%1169970|202102Flat
116997020210390 - 100%1169970|202103Flat
116997020210490 - 100%1169970|202104Flat

 

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.  

Message 4 of 10
525 Views
0
Anonymous
Not applicable
In response to Anonymous

‎07-08-2020 11:43 AM

Here's a sample of my projects table

 

projectskeyfiscalPeriodTime BucketKeyProject's Distribution
11699702020010 - 9%1169970|202001Front-Loaded
116997020200210 - 19%1169970|202002Front-Loaded
116997020200310 - 19%1169970|202003Front-Loaded
116997020200420 - 29%1169970|202004Front-Loaded
116997020200530 - 39%1169970|202005Front-Loaded
116997020200630 - 39%1169970|202006Front-Loaded
116997020200740 - 49%1169970|202007Front-Loaded
116997020200850 - 59%1169970|202008Front-Loaded
116997020200950 - 59%1169970|202009Front-Loaded
116997020201060 - 69%1169970|202010Front-Loaded
116997020201160 - 69%1169970|202011Front-Loaded
116997020201270 - 79%1169970|202012Front-Loaded
116997020210180 - 89%1169970|202101Front-Loaded
116997020210280 - 89%1169970|202102Front-Loaded
116997020210390 - 100%1169970|202103Front-Loaded
116997020210490 - 100%1169970|202104Front-Loaded

 

Here's my distribution Table


DistributionPercentageTime BucketTotal Forecast Helper

Flat0.100 - 9%3.90
Flat0.1010 - 19%3.90
Flat0.1020 - 29%3.90
Flat0.1030 - 39%3.90
Flat0.1040 - 49%3.90
Flat0.1050 - 59%3.90
Flat0.1060 - 69%3.90
Flat0.1070 - 79%3.90
Flat0.1080 - 89%3.90
Flat0.1090 - 100%3.90
Front-Loaded0.100 - 9%2.19
Front-Loaded0.2810 - 19%2.19
Front-Loaded0.2720 - 29%2.19
Front-Loaded0.1030 - 39%2.19
Front-Loaded0.0540 - 49%2.19
Front-Loaded0.0550 - 59%2.19
Front-Loaded0.0560 - 69%2.19
Front-Loaded0.0570 - 79%2.19
Front-Loaded0.0380 - 89%2.19
Front-Loaded0.0290 - 100%2.19
Back-Loaded0.020 - 9%4.80
Back-Loaded0.0310 - 19%4.80
Back-Loaded0.0520 - 29%4.80
Back-Loaded0.0530 - 39%4.80
Back-Loaded0.0540 - 49%4.80
Back-Loaded0.0550 - 59%4.80
Back-Loaded0.1060 - 69%4.80
Back-Loaded0.2770 - 79%4.80
Back-Loaded0.2880 - 89%4.80
Back-Loaded0.1090 - 100%4.80
Bell0.020 - 9%4.75
Bell0.0410 - 19%4.75
Bell0.0820 - 29%4.75
Bell0.1530 - 39%4.75
Bell0.2240 - 49%4.75
Bell0.2250 - 59%4.75
Bell0.1560 - 69%4.75
Bell0.0870 - 79%4.75
Bell0.0480 - 89%4.75
Bell0.0290 - 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, 

projectskeyfiscalPeriodTime BucketKeyProject's DistributionPercentageForecastHelper
11699702020010 - 9%1169970|202001Front-Loaded0.12.19
116997020200210 - 19%1169970|202002Front-Loaded0.282.19
116997020200310 - 19%1169970|202003Front-Loaded0.282.19
116997020200420 - 29%1169970|202004Front-Loaded0.272.19
116997020200530 - 39%1169970|202005Front-Loaded0.12.19
116997020200630 - 39%1169970|202006Front-Loaded0.12.19
116997020200740 - 49%1169970|202007Front-Loaded0.052.19
116997020200850 - 59%1169970|202008Front-Loaded0.052.19
116997020200950 - 59%1169970|202009Front-Loaded0.052.19
116997020201060 - 69%1169970|202010Front-Loaded0.052.19
116997020201160 - 69%1169970|202011Front-Loaded0.052.19
116997020201270 - 79%1169970|202012Front-Loaded0.052.19
116997020210180 - 89%1169970|202101Front-Loaded0.032.19
116997020210280 - 89%1169970|202102Front-Loaded0.032.19
116997020210390 - 100%1169970|202103Front-Loaded0.022.19
116997020210490 - 100%1169970|202104Front-Loaded0.022.19



 

Message 5 of 10
497 Views
0
jdbuchanan71
Super User
Super User
In response to Anonymous

‎07-08-2020 11:57 AM

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]
)

 

Message 6 of 10
495 Views
Anonymous
Not applicable
In response to jdbuchanan71

‎07-08-2020 12:22 PM

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.

 

 

Message 7 of 10
491 Views
0
jdbuchanan71
Super User
Super User
In response to Anonymous

‎07-08-2020 12:28 PM

What calculation are you wanting to do?

Message 8 of 10
489 Views
0
Anonymous
Not applicable
In response to jdbuchanan71

‎07-08-2020 01:17 PM

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

Message 9 of 10
476 Views
0
v-yiruan-msft
Community Support
Community Support
In response to Anonymous

‎07-09-2020 02:43 AM

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 10 of 10
453 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All