Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
victoria_g06
Helper I
Helper I

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

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] >= 11, [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) 

PTE.png

 

 

Thank you for taking a look! 

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

Sakiko_0-1693275702692.png

 

View solution in original post

7 REPLIES 7
victoria_g06
Helper I
Helper I

Thank you @DataNinja777 for your input! 

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

 

 employee rows.png

 

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. 

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

 

Sakiko_0-1693271873219.png

 

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? 

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.

Sakiko_0-1693275702692.png

 

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. 

My pleasure, victoria_g06!   

DataNinja777
Super User
Super User

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:

Sakiko_0-1693269025539.png

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.  

Sakiko_1-1693269188213.png

 

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, 

 

Sakiko_3-1693270133369.png

 

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.  

Sakiko_2-1693269551119.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.