DAX Fridays! #25: Wrong Grand Totals in Power BI

[@satishbadiger8625]

Hey, thankyou so much I had been struggling with this issue since a week now. I tired so hard finding for the solution but nothing worked. You came to the rescue. Thank you so much again.

[@CurbalEN]

My pleasure!

[@swatisneh2012]

When I nowhere find solution for my problem, you are the only one who solves it in one go with clear picture. Thanks ma’am!! It’s wonderful learning from you.

[@CurbalEN]

At your service!

[@scottemiller4501]

Thank you! A simple elegant explanation of what, why & How to fix it! Keep up the great work

[@CurbalEN]

Thanks!

[@FlareSoul69]

OMG!! Thank you so much!!! It took me 3 days... and couldn't figure it out! You're such a blessing!!!

[@CurbalEN]

Glad it worked! /Ruth

[@gkakun]

Brilliant!!! You just solved me a problem I'm dealing with for 2 years!

[@CurbalEN]

Excellent ! Glad it was useful! /Ruth

[@lovenisarg]

Thank you for such a lovely explanation. Kudos to you. This helped me a lot. Earlier I always used to create new column in the table and get the totals right in matrix but now I have much more clarity how to do it for measures thanks to you I just now implemented this in my recently developed rpeort and removed the calculated column an dthis change worked perfectly. Thanks once again for such lovely video.

[@sansden]

Thank You Ruth ....Your tutorial are always a savor for my problems ...Love it ..

[@CurbalEN]

Glad to hear!

[@ashwinmanickam]

Thank you so much!!!! You are a blessing to the power bi community! i have been breaking my head over this for quite sometime!

[@CurbalEN]

Glad it work!!

[@SonNguyen-zj1gj]

Tks alot, your solution can solve my year of headache .

[@abhijeetghosh27]

Thank you @Ruth ! You and your videos are just amazing. 😀👍Once again it saved my day. Thank you ! 💗😊

[@efrainmartinez4465]

Further than to learning Dax language, I have improved my English. Thanks Ruth!

[@CurbalEN]

Yes! Double benefits!! Really happy to be able to contribute :) /Ruth

[@slowchrno]

Tks a lot for this Ruth. Started using and learning PBI in the last couple of months and your videos have helped a lot. I'm trying to do a contribution margin variance analysis and the SUMX DAX were giving me hell. I was triying a lot of different stuff with SUMMARIZE and GROUPBY but was getting nowhere. I think with this I finally got the last piece of the puzzle. Thanks again!

[@MrMattDickens]

This saved me so much time. I hope $5 was ok. I think your work is amazing.

[@wozniakken]

This is amazing, thank you so much for making these. This is the only video that actually makes sense. I did it and it worked!!!!!!!!!!!

[@CurbalEN]

Great!!🥳🥳

[@MrMattDickens]

Thanks!

[@CurbalEN]

And super thanks back 😊 !!

[@nashiki4792]

Thank you so much. One’s more you saved my report :D

[@KimberleyCanoute]

Thank you so much for this!!! Really appreciated as this solution helped me fix a big problem with critical BI report I had to submit this week.

[@CurbalEN]

Well done!!

[@gregschalk5095]

Awesome! Love Dax Fridays! I always learn something new

[@CurbalEN]

Me too :) /Ruth

[@arubeirubu]

Wow this help me a Lot, great explanation. Greetings from El Salvador.

[@darlenesoukhaphonh8437]

This really helped! Easy to follow along, and you explained it so well.

[@CurbalEN]

Yey!! Thanks for the feedback! /Ruth

[@imranayasmin6549]

Thank you so much. It helped me solve my table summary total at my work. 🙂

[@markstanfill]

Thanks very much for the personalized instruction! :) Your explanation helps. I think this underscores how careful you have to be with data validation. I'm going to make sure that I test any summary columns against both a full data set and filtered values from now on to look for this class of errors. Cheers!

[@CurbalEN]

+Mark Stanfill You are most welcome :) You are right, creating the measure is one thing but validating is everything! 🤓 Have a great weekend! /Ruth

[@saibigdata4046]

Hi Ruth, Thank you so much for such elaborated explanations

[@CurbalEN]

+Sai Bigdata Hi Sai, you welcome! :) /Ruth

[@fraserjewing]

Perfect. I was working on solving this for the last 2 days. Popped in your formula and bingo, its working.

[@CurbalEN]

It is rare that happens, but hey! Who is complaining? ;) /Ruth

[@gildardorojasmartinez7433]

Thank you so much you saved my life 😊. all the way from Mex...

[@Rrabbit_swim]

Thanks Ruth :), I have a great story about this. One day a friend came to me and say we have 100% participation rate in an exercise. We were all excited until we saw that the total didn't add up. We left class after sad

[@CurbalEN]

But you were smart to know that 100% was probably too good to be true. True testament of data literacy ;)

[@vineetyoutubr5]

Very helpful video Ruth.. I was facing the same issue with my utilisation file. Now it's been fixed 😁

[@CurbalEN]

Yey!!! Excellent :) /Ruth

[@SmartBimson]

It's really interesting and it's awesome that you do personalized videos 👍🏻 greetings from my vacation ☀☀☀

[@CurbalEN]

+SmartBimson Checking DAX videos on your holidays? Close your computer and enjoy your time! 🏖 /Ruth

[@SmartBimson]

Curbal I Did Ruth, but today I'm already looking forward a new Dax Friday ! Have a great day and thanks a lot for all your effort ! Hasta luego 😉🌞

[@CurbalEN]

+SmartBimson The video is up and ready to go later this afternoon, hope you will enjoy it ! /Ruth

[@opod84]

Thank you for the explanation! This helped me out of a bind!

[@caro1853]

You were my salvation... THANK YOU!!

[@CurbalEN]

My pleasure!

[@EricaDyson]

Brilliant. The only explanation that I actually understood! I had a workaround.. now I don't need it!

[@CurbalEN]

🥳 /Ruth

[@sauravsinha6939]

what if there was a matrix with product category and sub catergory and other more in the matrix and they all showing sales amount there we cannot use hasonefilter. Please suggest that too

[@EminUzunN]

Nice Tip. Thanks Ruth.

[@CurbalEN]

+Emin Uzun You welcome admin, have a nice weekend, /Ruth

[@tanorniang3888]

Very amazing!!! Thank a lot

[@ellukayou]

If I, out the pdructs names in the midle table, can i use it for the fliters?

[@janfranck5346]

Very nice, Thank you Ruth :)

[@CurbalEN]

Hi Jan! Some are really cool right? /Ruth

[@musab2025]

Life saver!! You are awesome!

[@felipebizarre]

any way of doing the right total amount but instead of adding it subtracts? this has been so helpful so thanks so much !

[@MrRagerrrr7777]

hi there! what if youre using a matrix table and you have 3 fields in the row? how would you use hasonevalue when you have multiple row fields in a matrix table? my problem is the row level total is correct but the grand total is incorrect. Please help thanks!

[@HabariYaMere]

Thanks Ruth! very helpful and this has worked for me before on measure but not I have a measure that takes the max of a value and when I try to wrap it in the if statement I either get the max value or a wildly high number. here's the latest attempt which just gives me the max of the 5 distinct 'Project Total amount' values Amount = if ( HASONEVALUE('Project Listing'[ Project List.Practicearea]), [Project Total Amount], SUMX(values('Project Listing'[Project List.Practicearea]), [Project Total Amount])) the project total amount measure is just Project Total Amount = MAX(Header[ProjectTotalAmount])

[@CurbalEN]

Hi Meredith, could you ask in the Power bi Community? Thanks! /Ruth

[@stephanethia]

Hi, i don't understand why in the Tot Amount column, some rows are empty (because of stock table filter as you said). I wanted to see the Pbix data to understand, but, on the download center, we find all pbix, but not for this one :p ... thank you for all your tutorial. very helpfull

[@sapbo2213]

Hello Ruth,Thanks for this useful video //Vicky

[@CurbalEN]

You welcome Vicky! /Ruth

[@KanhaiyaLal-ze3ri]

Hi how to calculate columns Grand total value with two table.

[@mdrams4027]

useful, thank you!

[@sisuchi18]

Just a quick question :) Are you sure the HASONEVALUE() is needed? It seems to also work when using Right total amount = SUMX( VALUES( StockTable[ProductName]) , [Tot Amount] ) . Considering that SUMX() goes through each row of the table to calculate its outcome, the HASONEVALUE might not be needed. Would love to hear what you think.

[@cedricgacond1990]

Hello Ruth, Unfortunalty it doesn't work if we are trying to add the of numbers of days that are between two dates. Do you have an idea on how this can be done?

[@burmaabugii4020]

thank you so much

[@DanielWeikert]

Hi Ruth, one question you might help me with. In advanced Dax if often encounter wrong totals. I have to work with virtual tables to correct them. What I would like to know is if there is a certain rule which tells me in advance whether the totals will be wrong? Do you know that? Thanks and have a great weekend

[@bryancajavilcavinces7124]

Hi ruth, do you have an idea for how to remove the total (in a matrix) for just a specific column? I have a matrix with 6 columns, one of this is the "per salary" and in the total appears the sum of this column, i want to hide or remove the total for just this column. Thanks

[@karthikapethurajan6615]

Thank you thank you thank you sooo much!!!!!!!!🤩

[@mahindrabisram2923]

Thank you for this informative video. Can you enhance this to show how to make this generic so it will work for any dimension column being used for filtering?

[@CurbalEN]

How things are calculated depends highly on how the model looks like. If it is possible to do, I don’t know how to. Check in the power community, maybe somebody there knows! /Ruth

[@jimmyni1983]

Hi @Mahindra, I think your scenario also can be resolved using similar I replied to @Aonline. You just have to replace by your own dimension filters / slicer.

[@aonline-training]

Hi, Thanks for the video, has been very helpful. I got stuck at 1 place, when I have a slicer, which affects the result, then again the total appear's incorrect. can you help with that regards?

[@CurbalEN]

Hi, can you please contact the Power bi Community for support? Thanks, /Ruth

[@jimmyni1983]

Hey @Aonline. Maybe this can help you... I have a matrix with Disconnected Clients table in rows and a Slicer called User from original fact table (so, native from fact table). Basically I have created some VARs and two of them are Calculation when I do not have Users filtered and the other one is to bring me the correct result when I have Clients naturally filtered in the matrix and also when I have one or more values selected in User Slicer.

[@jimmyni1983]

Try to understand my measure: Total Horas Realizadas por Cliente e Usuário = VAR _RemoveClientes = ALL ( TS[Cliente] ) VAR _RemoveUsuario = ALL ( TS[Nome] ) VAR _Cliente = SELECTEDVALUE ( Clientes_Disconectados[Clientes] ) VAR _Usuario = ALLSELECTED ( TS[Nome] ) VAR _Calc = CALCULATE ( [Total Horas Realizadas]; _RemoveClientes; _RemoveUsuario; FILTER ( TS; TS[Cliente] = _Cliente ); FILTER ( TS; TS[Nome] IN _Usuario ) ) VAR _CalcFilteredUser = CALCULATE ( [Total Horas Realizadas]; FILTER ( TS; TS[Nome] IN _Usuario ) ) RETURN IF ( HASONEVALUE ( Clientes_Disconectados[Clientes] ); _Calc; _CalcFilteredUser )

[@jimmyni1983]

In case you need to know about [Total Horas Realizadas]: Total Horas Realizadas = SUMX( TS; TS[Horas] )

[@anuragbawankar685]

Thank you!!!

[@jacquelinehu91]

First, thanks a lot for this video. I tried this way to solve the wrong total for a thousands lines table and it makes updating the data very slow.

[@raultorvisco2406]

HI Ruth: I was diving in your file and a doubt came me up. Checking your queries I realized that there are some productNames present in "Stock Table" (E.g. "Clothing"), but not present in "Manufact Table". Additionally you have created "Both Filtered directions relationships", which is not usual. My question is, What should you do if you want to show all product names with all stock values and all manufacturing values?, -Should you create a new Table combining unique values from these two tables (Appending) and then relate with the aforementioned tables (instead of ProductID Table)? -Once created, should you pick up the Product Name from this new table if you decide to use single filtering direction?, selecting both filtering direction, it won't be a big deal. This is a real and common Many to Many case you can find in a real organization. Am I right in my reasonning? Thanks for the answer in advance.

[@CurbalEN]

Hi again :) Did I create the both relationship or was it there by default? Power BI did that in the beginning. I think tomorrow’s DAX Fridays! Video will or might answer your question, stay tuned :) /Ruth

[@raultorvisco2406]

Great, I will!

[@CurbalEN]

I have to record the video again due to poor sound quality. I will try to do it in the weekend :)

[@raultorvisco2406]

Hi Ruth Ok, take your time. In the meantime I was re-watching your videos about relationships... really helpful, but still having some doubts about this. Thank you for your patience.

[@CurbalEN]

Thanks for your patience :) /Ruth

[@ankitamalhotra5761]

Hi Ruth, I am facing the wrong totals for negatives, its showing the 0 for the negatives values, will this help in that case? thanks

[@CurbalEN]

Hi Ankita, Hard to tell without the details, post in the power bi community to get help: m.kzbin.info/www/bejne/pYmpgJiYqtuFsJI /Ruth

[@bailjr]

Can you please do one where the totals are correct, but the amounts in the columns are incorrect.

[@guusvanzuylen6501]

Hi Ruth, How aboub this: Ta2 = CALCULATE(SUM(ManufactTable[Amount]), StockTable)

[@CurbalEN]

Thanks! :) /Ruth

[@lenzypetty1097]

Nice approach approach. Works great in this instance. Not sure if any limitations, but works very good here. Also, you can also just do a left join in Query Editor, and avoid the situation all together.

[@CurbalEN]

With bigger models , joining tables is not an option, that is why I showed this trick :) /Ruth

[@nelgarmartinez5979]

what if I have just one database with one column (Units) and other one with two Categories (Sales / Fcst), I need to calculate the ABS variance, but the total is incorrect. How can a fix that?

[@raultorvisco2406]

HI Ruth: The link to PowerBi file doesn't work. This message appears instead : " Sorry, the page you are looking for is not found". Could you please reupload the file?

[@CurbalEN]

Hi Raul, here is the new link: gofile.me/2kEOD/MD487TiUq /Ruth

[@raultorvisco2406]

Hi Ruth, thanks! File downloaded.

[@CurbalEN]

Excellent! /Ruth

[@weslleyhenrique2989]

Very Good!

[@CurbalEN]

+Weslley Henrique Thanks Weslley!! Appreciate the feedback! 😊 /Ruth

[@mazarata73]

Thanks a lot for this. It's great it works. Let me share a tought I have. My understanding is, that VALUES(ProductName) returns a list of unique values found in that column under the valid filter context. Since there is no filter context on the level of the "Total", that list should be again the complete list of unique values found on the column "ProductName". Where is my knot? Thanks and regards.

[@kingofhearts6702]

@Curbal, I have the same doubt, pls can you help

[@riazahmedshaik]

What if add another dimension? how to fix it? Please help me

[@rajeshkhanna8709]

Hi Can you please share sample dataset, so that i will practice once

[@CurbalEN]

All my datasets are available in the download center :)

[@rajeshkhanna8709]

@@CurbalEN except this one

[@CurbalEN]

Then I I am.afraid I dont have it :(

[@Simon-vc1wk]

If you have to correct measures by using IsInScope or HasOneValue it's creating a problem in itself. As we would need to do that for all possible permutations of a table that a user of your report could create their own visuals from your model once published, which is dangerous. MS should fix Measure Totals issues in PBI.

[@abhishekpandey9186]

What if the dimension to be used is not defined, anyone can use any dimension here , so what should we use with the haso evalue, iffiltered or hasonefilter.

[@valiabis]

Hello. Can i have this for the below measure? =if(sum('Earnings Table'[Revenue])>10000,sum('Earnings Table'[Revenue]))

[@hafizakhairalubna5183]

Hello Ruth, I've problem with my dax formula, column totals are not equal to monthly total Forecast1 = if( CALCULATE(sum('dbprojects tbl_t_project_impact'[actual]))=0, SUMX('dbprojects tbl_t_project_impact',CALCULATE(SUM('dbprojects tbl_t_project_impact'[plan]))), sumx(values('dbprojects tbl_t_project_impact'),CALCULATE(SUM('dbprojects tbl_t_project_impact'[actual])) ))

[@ahmedal-dossary4386]

Impressive, this is a cool video. Do you have a video on consolidation and elimination when dealing with financial statement? If you happen to have a parent company that has many subsidiaries and you want to remove inter-company transactions at the corporate level. That would be an interesting topic. Thanks!

[@CurbalEN]

Nothing on financial statements so far, but it is on my list :) /Ruth

[@ahmedal-dossary4386]

Looking forward to it.

[@kellyeaster3163]

Thank you!

[@CurbalEN]

You welcome! /Ruth

[@raghuram3507]

why grand toal in MTD of dax always shows last value rather than total?any idea...

[@CurbalEN]

Check this video to see if it solves your problem: m.kzbin.info/www/bejne/q5ergIKCmc6SoM0 /Ruth

[@raghuram3507]

thank u for your quick reply... i have gone thorugh the link ,here it solves for grand total for functions like sum ,avg.but when i am using TotalMTD the subtotal at year is showing value of last month of that year instead of sum of all years...can you please help why it calculates like that

[@raghuram3507]

@@CurbalEN one small correction in above comment... it doesn't show sum of all months in the year at year subtotal

[@vinayakvnair5729]

you made my day!!

[@CurbalEN]

🥳 /Ruth

[@mathijs9365]

U could do a sumx with the table Value(stocktable[productname])

[@CurbalEN]

+Mathijs Thanks Mathiijs! Have a great evening! /Ruth

[@lifeisstr4nge]

I don't get it. Why even create measures for shit like this if you can add the item and sum it? Could someone explain this to me?

[@weslleyhenrique2989]

Can I use ALLSELECTED instead of VALUES ?

[@CurbalEN]

+Weslley Henrique You can download the file and try it yourself to see what happens 😉 /Ruth

[@changeyourmind-p1n]

Great

[@mis5995]

Hi, This is not working for me, can you please help me out. I am using below DAX. Less than 30 Days $ = IF(HASONEVALUE('Ageing Detail'[Branch]), DIVIDE( SUM('Ageing Detail'[Less than 30 Days]), AVERAGE('Ageing Detail'[ROE]) ), SUMX(VALUES('Ageing Detail'[Branch]), DIVIDE( SUM('Ageing Detail'[Less than 30 Days]), AVERAGE('Ageing Detail'[ROE]) )))

[@vinceslavov7629]

Thank you !