Forum Discussion

mork's avatar
mork
Helper V
10 years ago
Solved

Help with DAX formula

Hello all, I have two tables. One with my resources like bellow.   ResourceName           ExpectedChargeability      Resource1                          0.9      resource2                        ...
Need Help
  • sdjensen's avatar
    sdjensen
    10 years ago

    Sorry I was a bit too fast.. I tried to recreate your model and this formula seem to work for me... My table names might be different than yours...

     

    DIVIDE( 
	SUM(Capacity[ChargeableWork]); 
	SUMX(Capacity; CALCULATE( SUM( Capacity[Capacity] ) ) * CALCULATE( SUM( ExpChargeability[ExpChargeability] ) ) ) 
)
sdjensen's avatar
sdjensen
Solution Sage
10 years ago

Hi,

 

Could you please provide the expected result - I don't see how the sum of all the Expected values will make sence on the Total level.

mork's avatar
mork
Helper V
10 years ago

sdjensen

 

Ok so let's take for example the resource1 from the tables I provided.

 

For chargeability1 I want to just divide chargeableWork by Capacity. 

So in week 1 I'll get

 

30/40=0.75 which is 75%

 

For chargeability2 I want almost the same thing except I want my formula to take into consideration the expectedChargeability which for resource1 is 0.9

So according to my formula for week 1 I'll get

 

30/(40*0,9)=30/36=0.83 which is about 83%.

 

Then I want to put the chargeability2 in a card and have a slicer with the ResourceName and filter accordingly. 

When not filtered I want to see the average % of all resources for all weeks.

 

Unfortunatelly my formula for chargeability2 doesn't provide the correct outcome. 

 

DIVIDE( SUM([ChargeableWork]) ; SUM([Capacity]) * SUM(Table1[ExpectedChargeability]))

 

 

 

 

My formula for chargeability1 works though

 

DIVIDE( SUM([ChargeableWork]) ; SUM([Capacity])) 

  • sdjensen's avatar
    sdjensen
    Solution Sage
    10 years ago

    Could you try with 

     

    DIVIDE( SUM([ChargeableWork]) ; SUM([Capacity]) * CALCULATE(SUM(Table1[ExpectedChargeability])))
  • mork's avatar
    mork
    Helper V
    10 years ago

    sdjensen unfortunatelly nothing changed.

  • sdjensen's avatar
    sdjensen
    Solution Sage
    10 years ago

    Sorry I was a bit too fast.. I tried to recreate your model and this formula seem to work for me... My table names might be different than yours...

     

    DIVIDE( 
	SUM(Capacity[ChargeableWork]); 
	SUMX(Capacity; CALCULATE( SUM( Capacity[Capacity] ) ) * CALCULATE( SUM( ExpChargeability[ExpChargeability] ) ) ) 
)