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

      • mork's avatar
        mork
        Helper V
        10 years ago

        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.