Calculate SUM from a calculated measure

[nareshr89]

I have created a measure which calculates Employee requirement and the calcualtion is paid time/ working days. This works as expected in periodic manner for state level. But, my requirement is to add up the obtained values of each state to calculate the country level employee requirement.  

Ex - Region A = paid time of region A/ Working days of region A

Region B = paid time of region B/ Working days of region B

Country = Sum(Region A + Region B).

The new DAX is required for country level.

[tamerj1 · Accepted solution]

@nareshr89 

Following to our virtual meeting, following is the workable code. 

FTEsConso =
SUMX (
    CROSSJOIN ( VALUES ( table[Att] ), VALUES ( 'table1'[region] ) ),
    CALCULATE (
        DIVIDE ( CALCULATE ( [TotalValue], 'table'[Att] = "paid_time" ), [WD], 0 )
    )
)

[nareshr89]

Thanks a lot for your help Tamer, it worked as expected!

[Anonymous]

Hi @nareshr89,

Did @nareshr89 's suggestions help with your scenario? if that is the case, you can consider Kudo or Accept his suggestions to help others who faced similar requirements.

If that also doesn't help, can you please share a pbix or some dummy data that keep raw data structure with expected results? They will help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[tamerj1]

Hi @nareshr89 

This should return the desired result at both levels

EX =
SUMX (
    VALUES ( Employee[Region] ),
    CALCULATE (
        DIVIDE ( SUM ( Employee[Paid Time] ), SUM ( Employee[Working Hours] ) )
    )
)

[nareshr89]

What needs to be used in the Employee[Region], currently i have a region column based on which the results are not coming.

[tamerj1]

@nareshr89 

I'm sorry. Would you please clarify your reply further? Did you try the suggested code? What outcome do you get when you try it?

[nareshr89]

I tried the suggested code, but was not able to get the desired result also i was not clear on what needs to be used in the place of Employee [Region]

[tamerj1]

Can you please share the code that you've used and a screenshot of the results

[nareshr89]

Expected Result is around 1206

[tamerj1]

Try to put the code of "paidTime" directly inside "DIVIDE" rather tgsn using the variable

[nareshr89]

If i store the evaluated employee requirement result in a column, then at a quarterly level it doesn't pickup the right values. Almost the value doubles.