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

 

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

12 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    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's avatar
      mork
      Helper V
      10 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        10 years ago

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

  • Baskar's avatar
    Baskar
    Resident Rockstar
    10 years ago

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

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    10 years ago

    Try this:

     Medida 5 =

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