POWER PIVOT DAX measure total not adding up correctly, two measures in IF statement

[victoria_g06]

Hello everyone , I hope I can explain my issue concisely. 

I've read about how the subtotal in power pivot measures are not 'wrong' as they need the SUMX to iterate through the rows.. but all examples I've seen are general value calculations (total sales related to months or filters etc).

I have the below measure to calculate the percentage of part time equivalent to a monthly worker. They work to a total of 152 hrs in a month but a monthly worker has 164.67 so I can't use the same formula for both. 

PTE4 currently returns no subtotal, other variations either show the total of DivMpay or DivFNpay but I need the combination of both from this measure to show.

PTE4 =
IF (
    HASONEVALUE ( Hours[Pay ID] ) = TRUE (),
    IF ( [DivMpay] >= 1, 1, [DivFNpay] )
)

All measures for this measure PTE4 below

Total_hours =
SUMX ( Hours, Hours[Pay Hrs] * 1 )

The fortnight calculations are quite involved and could be more condensed I guess but this is what I have so far.

MPD=

MAX(Hours[Number of Pay Periods])

FN Hrs =

SUMX(FNHoursCalc,FNHoursCalc[HOURS]*1)

FNHRS =

[MPD]*[FN Hrs]

DivFNpay =

DIVIDE([Total Hours],[FNHRS])

Monthly calculations below

Mhrs =

SUMX(MonthCalcHrs,MonthCalcHrs[HOURS]*1)

DIVMpay =

DIVIDE([Total Hours],[MHrs])

This screen shot shows the different measures and the right hand side is the total it should show (12.2)

Any insights would be very much appreciated! (Also let me know if you need to see relationships tables) 

Thank you for taking a look! 

[victoria_g06]

Thank you @DataNinja777 for your input! 

The pivot has the employees split into their departments as per below.  

 

I dont have an issue with the sum of each [DivMpay] and [DivFNpay] its when I need to join them in the IF statement then I need their totals to join as per their IF statement answer.

So each monthly person needs to show 1 (from DivMpay)and each fortnight person has their decimal amount (from DivFN pay) both together add up to 12.2 in my example but I can't get that to show in the PTE measure. 

I've changed my measure as per your example to SUMX and VALUE before the DIVIDE but it still doesn't show the amounts joined. 

[DataNinja777]

Hi @victoria_g06

Thanks for the information.  I think in that case, you need to sumx over the table(s) which contain the fields you are putting on your pivot table rows for the subtotalling to work.  Because the screen print below only shows the field (column) name without the table name, I cannot specifically write the formula, but if you could tell me the table names along with the column name which you would like the subtotalling to work properly, I will be able to rewrite the suggested sumx formula for you.  Do the fields put on rows below belong to only one dimension table (like employee table) or multiple dimension tables (i.e., employee table and branch table which have a relationship with one another)?  

[victoria_g06]

Hi @DataNinja777 

Thank you , All those fields are on one table called "Hours" which the measures are looking at a column called "Pay Hrs" in the same table.  I'm happy to send you the workbook if you like? 

[DataNinja777 · Accepted solution]

Hi @victoria_g06

Thanks for the additional information.  I should have realized that it was in the 'Hours' table from our earlier conversation😊

In that case, I suppose the following sumx measure should fix the subtotalling inconsistency generated by the division function.

[victoria_g06]

thank you so much @DataNinja777 ! I never thought to use SUMX for the whole table. (I'm still learning about DAX though.) Many thanks for your assistance!

I used your formula for each DIVMpay and DIVFNpay and then I added them together in a separate measure named PTE which now shows the correct total. 

[DataNinja777]

My pleasure, victoria_g06!   

[DataNinja777]

Hi @victoria_g06 

Although this is not the cause of the summation error, multiplying by 1 does not have any impact, so I would simplify the formula by removing it, also, I guess sumx is not needed below and a simple sum would do the same job the case below:

Regarding the summation error which you are enquiring below, the subtotal inconsistency from DivFNpay was generated by the formula not using sumx. Sumx function will force the addition to be correct over a table which you are sumxing (if there's such a verb as that).  "Divide" function used in [DivFNpay] measure just divides the total row number as well, so the summation of the rows will not match. Therefore, you need to use sumx function to force it to generate correct subtotalling of what's on the rows.  

May I ask you what are the row fields you are subtotalling in the screen shot above?  Is that a field named Hours[Pay ID] ?  In that case, 

would be forcing the subtotal at the bottom to be correct over the table of values(Hours[Pay ID] ).

Also, the blank value in the subtotal row for the measure PTE4 is caused by if(hasonevalue formula because subtotal row is not considered to be "hasonevalue" of whatever field is on the rows, and therefore, it is returning a blank value for the subtotalling.  

Please let me know if you need any further clarifications or if you'd like me to look at your data model.