Dividing measure returning false results

[S3]

ello everyone, 

I have two measures which I didvided by each other, and the result Power BI gives me is not the same as when I calculate them manually. Here are the measures (please know that even though I have all date columns as Type Date/Time, I still get an error that I can't use it Type Text with Date and that I should use the FORMAT function):

rounds measure = SUM('Table'[rounds])

rounds = 

CALCULATE (

[rounds measure],

FILTER('Dates',

(FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.01.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "28.02.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.03.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.04.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.05.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.06.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.07.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.08.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.09.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.10.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.11.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.12.22")))

seconds measure = SUM('Table'[seconds])

seconds = 

CALCULATE (

[seconds measure],

FILTER('Dates',

(FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.01.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "28.02.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.03.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.04.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.05.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.06.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.07.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.08.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.09.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.10.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.11.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.12.22")))

seconds/rounds = DIVIDE ([seconds],[rounds])

(It's the same whether I use DIVIDE() or simply /)

There's a column called Game Type.

Here is what I get from diving them, even though if I manually calculate seconds by rounds I get a different answer:

Game Type		rounds		seconds		seconds/rounds
Game 1		3254		14558.57		4.28
Game 2		609		2187.99		3.50
Game 3		718		2230.60		3.05

[S3 · Accepted solution]

Hello Adescrit,

thanks a lot! This gives me the correct result (the same when I divide them manually). 

I also implemented the advice given me above about not comparing Dates to strings and using || along with your solution, so now I have this:

CALCULATE (

DIVIDE([rounds], 'seconds]),

FILTER('Dates','Dates'[Date] = ENDOFMONTH('Dates'[Date])))

and it workds perfectly, thank you again

[daXtreme]

@S3 

If you have to write such ugly code, it means you're not doing it right (sorry!). Converting dates into strings and then comparing them to strings (using || on top of that) is a BIG NO-NO. This code is hard to read, hard to maintain and prone to errors. If you want to compare a real date to a date literal, here's how you create a date literal:

Either:

DATE( year, month, day )

where the arguments are integers, or

dt"yyyy-MM-dd"

If you need to check if a value is in a set of values, use the IN operator instead of "||" and the anonymous table generating construct:

{val1, val2, val3,...}

Please revise your setup first.

[S3]

I have implemented your tips with the solution below from Adescrit and I wrote this:

CALCULATE (

DIVIDE([rounds], 'seconds]),

FILTER('Dates','Dates'[Date] = ENDOFMONTH('Dates'[Date])))

and it works, thanks a lot again

[S3]

Hello daXtreme, 

Thank you for letting me know that my code is very wrong, I just wanted to calculate the value of the last day of each month, because in the dataset they put the total in each day, and not the difference, so in order to calculate the total of January, I have to choose only January 31st. 

I didn't really understand how to implement what you've suggeted, is each a measure on its own? I've tried the first one but Power BI told me that "day" is not accepted. If you have time and can let me know how to implement what you've suggetsed I would be very grateful to learn. 

I tried now this measure instead: 

Rounds=
CALCULATE (

[rounds],

FILTER('Dates', 'Dates'[Date] = ENDOFMONTH('Dates'[Date])))

and the same for seconds, and it works, as in it gives me the same value at the end of each month without comparing dates to strings and using ||. So the ugly part is over, however, the calculation of seconds/rounds is still giving the same false result..

[daXtreme]

By the way... A more efficient measure is this:

Rounds =
var EndOfMonthDate = ENDOFMONTH('Dates'[Date] )
var Output = 
    CALCULATE (
        [rounds],
        KEEPFILTERS( 'Dates'[Date] = EndOfMonthDate )
    )
return
    Output

That's because it uses KEEPFILTERS.

[S3]

Thank you for the tip. So now that I calculated seconds/rounds correctly, when I put them in a table to diffrentiate between the games (Game 1, Game 2, Game 3) throughout the months, I get a value in the totals that's slightly different than the avergae I calculate manually. 

Here is the measure I did for each column:

seconds/rounds Game 1 =

CALCULATE(

'Table'[seconds/rounds],

KEEPFILTERS('Games'[game_name]= "Game 1"))

seconds/rounds Game 2 =

CALCULATE(

'Table'[seconds/rounds],

KEEPFILTERS('Games'[game_name]= "Game 2"))

seconds/rounds Game 3 =

CALCULATE(

'Table'[seconds/rounds],

KEEPFILTERS('Games'[game_name]= "Game 3"))

In the total I should get 4.49 instead of 4.47 and 3.60 instead of 3.59

Month		Game 1		Game 2		Game 3
January		4.72		3.78		3.27
February		4.71		3.78		3.26
March		4.61		3.71		3.20
April		4.42		3.55		3.06
May		4.45		3.57		3.08
June		4.27		3.42		2.96
July		4.29		3.44		2.97
Total		4.47		3.59		3.11

[daXtreme]

I'm afraid that's not how you do it. Slicing and dicing in measures is not usually done via putting a filter in them for a specific attribute's value but rather by placing the attributes in columns/rows of matrix/table/visual and then letting the measure work on the filter context set by these selections automatically. On top of that, you should never place the table name in front of a measure, in contrast to column names where you always have to precede them with the table name.

So, your measure should just be:

[seconds/rounds]

and to obtain the matrix above you just put the attribute's values on the columns (Game 1, 2, 3) and drop the measure onto the matrix. A good measure needs to sense what kind of attribute the context has been set to and react accordingly.

If you want your measure to return averages over the months, you have to code the measure accordingly. It won't do it on its own, it has to be coded. I'd say something like this (but don't know the whole story, so it might be a bit off):

total seconds = SUM('Table'[seconds])

[seconds/rounds] =
averagex(
  // MonthID must be a unique identifier
  // of each month. Not just the month
  // number. A unique id across all years.
  distinct( 'Table'[MonthID] ),
  [total seconds]
)

I kindly suggest you learn the basics of Power BI from here for instance. This looks to be a free course and Enterprise DNA have top-quality material about Power BI.

[S3]

Hello daXtreme,

thanks a lot for the link suggetsion, I will do it for sure. I also understand your point about the matrix table, measures and filters. I think I have spend a bit too long overthinking matters and it's much simpler actually. 
I created average measures for the seconds and rounds and the seconds/rounds, the matrix has now a total column as well as a total row, and it's much better this way of course. The only thing is that it still gives me the same results in the total row (4.47 instead of 4.49)

I really appreciate your tips and help and won't take more of your time, but if you have another idea regarding to why is the results showing this I would appreciate it. I have a unique MonthID because I'm using the MonthNr (202201,202202) so it can't be duplicated. 

[daXtreme]

To diagnose issues like this I'd need a file with sample data in it demonstrating the issue. Just a minimal file. You can place here a link to a shared drive with such a file. If you do, don't forget to set access rights accordingly. You can use Google Drive, OneDrive, Dropbox... and many other services.

[S3]

Thanks daXtreme again, here is the file.

I would also like to ask about the last day in month measure, what if I don't have values on the last day of each month and would like to show the last value available for each month, would that be possible? 

Thanks again

[daXtreme]

Here's the file attached... I'd stongly advise to get familiar with the conventions of DAX -> Rules for DAX Code Formatting - SQLBI

I have not revised everything as I don't have time. Just added some changes and a measure that returns the last day where there's data. I have not revised any averages but if you want to average over months, then you have to write explicit code for it, as I've already mentioned elsewhere.

[S3]

Thanks a lot! I appreciate your help and will keep up the search to find the correct average measure. 

Again, thanks for your time, tips and help.

[S3 · Accepted solution]

Hello again, I find the solution to the aboce question 🙂 

https://sqlkover.com/calculate-a-semi-additive-average-in-dax/

[daXtreme]

Hi there.

Of course, it's possible. All you have to do is to find the very last day in the month that does have a value if there's no value for the last day in the month. That's not overly complex. I'll look into the file..

[Adescrit]

Hi @S3 ,

How about trying the following:

seconds/rounds = 
CALCULATE (
DIVIDE([seconds measure], [rounds measure]),
FILTER('Dates',
(FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.01.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "28.02.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.03.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.04.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.05.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.06.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.07.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.08.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.09.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.10.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.11.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.12.22")))

Does this give you the correct result?

[S3 · Accepted solution]

Hello Adescrit,

thanks a lot! This gives me the correct result (the same when I divide them manually). 

I also implemented the advice given me above about not comparing Dates to strings and using || along with your solution, so now I have this:

CALCULATE (

DIVIDE([rounds], 'seconds]),

FILTER('Dates','Dates'[Date] = ENDOFMONTH('Dates'[Date])))

and it workds perfectly, thank you again