Forum Discussion
incorrect total for measure
Hey ukeasyproj
This is a common problem because most users expect the Total row to just be the sum of the detail rows. Turns out that Power BI will evaluate the total row the exact same as the detail rows unless you specify differently. You can achieve this by using the HASONEFILTER function, because the row data will contain a filter and the grand total will not. An example measure would look something like this:
RealMonthlySum =
IF(
HASONEFILTER(Table1[Date]),
TOTALMTD(SUM(Table1[SalesAmount]), Table1[Date]), --Logic for Detail Rows
SUM(Table1[SalesAmount]) --Logic for Grand Total row
)If you want further explanation, you can watch a video tutorial I posted here.
Hope this helps,
Parker
Anonymous
Hey thanks for that, I can defintely use that
but my regular hours measure introduces another level of complexity where the hours cannot exceed 40, if they do, it takes it it as 40
for example if user 1 logged 56 hours for the selected 7 day period, it should display 40 instead, and when the grand total is calculated (it should use 40 not 56)
I am unsure of how to implement that logic for the grand total part
Gotcha, that condition makes it a little trickier. Try this:
measure = IF( HASONEFILTER(Table1[UserId]), IF( [Hours Worked] > 40, 40, [Hours Worked] ), SUMX( Table1, IF( [Hours Worked] > 40, 40, [Hours Worked] ) ) )Because of the extra step, you need to use SUMX to specify how you want to take that sum for the Grand Total. Then you will get something like this:
Hope this helps,
Parker
Anonymous
Hey thanks for that measure, it works fine
but when I add in additional columns to the table related to the user's meta data (such as user's supervisor name), the measure breaks
Is that field from a different table? Could you post a snippet of the data that you're working with?
Thanks,
Parker