topic Re: Calculate formula by 50% time of another value in DAX Commands and Tips

Calculate formula by 50% time of another value

usmanghani — Mon, 01 Aug 2022 09:50:28 GMT

I am using a Formula on New Measure;

Please walk though my problem.

Using A measure, I calculated percentages of specific work.

E.g.

Design Time = 30%

Procurement Time= 30%

Civil Time = 40%

Total = 100%

Project Duration is 30 Days

Now suppose 50% time of above each work is already consumed.

I Showed this by using a slicer which has 30 days and I put slicer bar on 15 days.

Now When I put Bar on 15 days (Total days are 30) that means 50% of work tome is completed.

I need 2 kinds of results

Design Time: 50% consume out of 100% according to Separate schedule

And

Design Time= 15% consume out of 30% according to whole project schedule

(Result-1) I want to show the design team that your 50% time is already consumed.

(Result-2) And total project weightage of design is 30% out of which 15% is consumed.

And which can be achieved by below formula

Formula = Divide (Nominator, denominator) or Divide (A & B)

Now when I put date slicer on 15 days (50% of total days) the formula should work as below

A = 50% of B Value

B = 100% of B Value

I want to make B as constant and A as 50% then the formula show me result.

How I can achieve this, please give any suggestions

Re: Calculate formula by 50% time of another value

thiagocamillo — Tue, 02 Aug 2022 14:16:58 GMT

Hi, Can you show tables, relations and measures? If possible share your project to for more details.

Re: Calculate formula by 50% time of another value

v-yadongf-msft — Wed, 03 Aug 2022 09:53:58 GMT

Hi usmanghani ,

According to your description, I created a table as below：

Please try following steps.

1.Create a measure called Total to calculate time all item spent. The time spent here is assumed to be 100

Total = SUM('Project'[Spend])

2.Create a measure called Percentage to calculate the percentage of time spent on all items.

Percentage = DIVIDE([Total],CALCULATE([Total],ALL('Project'[Item])))

3.Create a measure called Consumed to calculate time has been consumed.

Consumed = [Total] / 100

4.In report page, when you put slicer Bar on 15 days (Actually on day 16), you can show the design team that your 50% time is already consumed, which is the result-1 you want.

At the same time, you can show total project weightage of design is 30% out of which 15% is consumed, which is the result-2 you want.

^{Best Regards,}

^{Yadong Fang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}