Forum Discussion

rudiklein's avatar
rudiklein
Icon for Advocate III rankAdvocate III
9 years ago
Solved

SUMming in a SLICEd MEASURE

Hi,   I have a question which might already be in the forum, but it always seems that my situation is slightly different and non of the answers provided me with an solution. The annoying bit is tha...
Need Help
  • rudiklein's avatar
    rudiklein
    9 years ago

    Hi all,

     

    it took me a while, but I have managed to solve this issue myself (persistence pays).

     

    The answer is in the use of CALCULATE() and ALLSELECTED() (many thanks to The Definitve Guide to DAX by Alberto Ferrari and Marco Russo).

     

    Example measure : Legal Working Days = Calculate(Sum('CalendarMappingTable'[Legal Working days]);ALLSELECTED('FactTable'[Calendar Month])). This will honnour the selection made by the slicer for the "Calendar Month" field.

     

    You can then use the measure in yoyur calculations.

     

    Bye,

     

    Rudi

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

Hi rudiklein,

 

You can create a calculated column in Main table to return the Legal Working Hours:

LegalWorkingHours = RELATED(Table2[Legal Working Days])*8

 

Then you can create a measure:

Percentage = SUM('Table1'[Hours])/SUM(Table1[LegalWorkingHours])*MAX('Table3'[FTE])

 

 

Best Regards,
Qiuyun Yu

 

rudiklein's avatar
rudiklein
Icon for Advocate III rankAdvocate III
9 years ago

Hi all,

 

it took me a while, but I have managed to solve this issue myself (persistence pays).

 

The answer is in the use of CALCULATE() and ALLSELECTED() (many thanks to The Definitve Guide to DAX by Alberto Ferrari and Marco Russo).

 

Example measure : Legal Working Days = Calculate(Sum('CalendarMappingTable'[Legal Working days]);ALLSELECTED('FactTable'[Calendar Month])). This will honnour the selection made by the slicer for the "Calendar Month" field.

 

You can then use the measure in yoyur calculations.

 

Bye,

 

Rudi