Forum Discussion
Column total when using DIVIDE in rows not calculating correctly
Hi all,
Happy New year to everyone.
Have a question regarding the column total not working as expected when using DIVIDE to calculate the row.
Let me explain
Each week, we have £££ sales and # of units sold. We calculate a 3rd column for Rate
Measure: Rate = DIVIDE( sales, [units Sold] )
Please see picture below to explain a little further
If we add all the values for Rate we get 15,818. However, the Total row is doing this maths, 42,256/31.57 = 1,338
How can we keep the row DIVIDE but sum the results??
Anonymous assuming [Sales] and [Units Sold] are existing measure, add a new measure
Rate Meassure = SUMX ( VALUES ( Table[WeekNo] ), DIVIDE( [sales], [units Sold] ) )Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
3 Replies
Anonymous assuming [Sales] and [Units Sold] are existing measure, add a new measure
Rate Meassure = SUMX ( VALUES ( Table[WeekNo] ), DIVIDE( [sales], [units Sold] ) )Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Try this...
IF( ISFILTERED(WeekNumber), DIVIDE( Sales, UnitsSold, BLANK() ), SUMX( TableName, DIVIDE( Sales, UnitsSold, BLANK() ) ) )I know nothing about your data model so the table, field and measure names are guesses. Substitute the real table/field/measure names where appropriate.
