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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Frequent Visitor

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.



MAX(Hours[Number of Pay Periods])


FN Hrs =




[MPD]*[FN Hrs]


DivFNpay =

DIVIDE([Total Hours],[FNHRS])


Monthly calculations below

Mhrs =



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! 


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.



View solution in original post

Frequent Visitor

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




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.



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!   

Skilled Sharer
Skilled Sharer

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. 

Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors