Help with DAX formula

[mork]

Hello all,

I have two tables. One with my resources like bellow.

ResourceName           ExpectedChargeability

     Resource1                          0.9

     resource2                           0.6

     resource3                           0.7

    resource4                            0.5

The second table contains data from the timesheets my resources fill.

ResourceName               Capacity            ChargeableWork      week#

     Resource1                        40                           30                     1

     resource2                        40                             36                    1

     resource3                         32                             32                   1

    resource4                          32                             35                    1

     Resource1                        40                           30                     2

     resource2                        40                             36                    2

     resource3                         32                             32                   2

    resource4                          32                             35                    2

I have calculated the chargebility percentage with the following formula.

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

This formula works fine but I also want to calculate another chargeability percentage that takes into consideration the expected chargeability. Basically I want the same formula as above except I want to also divide by the ExpectedChargability. I tried the following but it didn't work.

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

But it doesn't work. It only works when I select individual resources from a slicer. When there is no filters I get a really small percentage. What's wrong with my formula? I'm using a measure to achieve this.

[Vvelarde]

Try this:

 Medida 5 =

AVERAGEX (
    DetailsByResource;
    DIVIDE (
        CALCULATE ( SUMX ( DetailsByResource; DetailsByResource[ChargeableWork] ) );
        CALCULATE (
            SUMX (
                DetailsByResource;
                DetailsByResource[Capacity] * RELATED ( Resources[ExpectedChargeability] )
            )
        )
    )
)

[Baskar]

Hi Mork
I think u created calculate measure , by the way i suggest u please create column with same formula. 

See , if u using the formula which u have right now it will give total sum of measurew level then it will wotking  value.but this is now right percentage.
U try to done by row level.

Correct me if am wrong 🙂

[Greg_Deckler]

Hmm, using your formulas, mine appears to work correctly. I get the same values for Chargeability2 whether or not I have a resource selected.

Here is a screen shot of my table (no slicer value selected)

My formulas (as measures) are:

Chargeability = DIVIDE( SUM([ChargeableWork]) , SUM([Capacity])) 

Chargeability2 = DIVIDE( SUM([ChargeableWork]), SUM([Capacity])) * SUM(Expected[ExpectedChargeability])

This is with the data you supplied and the tables related on ResourceName.

[mork]

@Greg_Deckler

Chargeability2 and Chargebility1 is a percentage. My numbers are random that's why you get 200% in chargeability2.

Try putting chargeability2 in a card in order to show the chargability of all the resources together. It gives me a really low percentage when it should give the average of the chargability percentage of each resource. And when I choose a single resource from a slicer the card shows the correct percentage.

[Greg_Deckler]

Using your data, I get 2.49 in my card, which matches the Total column in the table...?

[mork]

@Greg_Deckler

Ok just noticed that your chargeability2 formula is not like the one I posted. You are actually multiplying after the division while I multiply inside the division. You have misplaced a parenthesis.

I want to multiply the capacity with the expected chargability.

[mork]

Actually now that I did some calculations even when I select a single resource the percetage isn't correct. 

For example if I multiply the capacity of one resource with its expected chargeability and divide the the chargeable by that number then I get a number different from the one my formula is giving me. I want to perform the above calculation for all my resources and also be able to filter it by resource.

[sdjensen]

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]

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

Could you try with 

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

[mork]

@sdjensen unfortunatelly nothing changed.

[sdjensen · Accepted solution]

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