Divide not working as expected in DAX Measure

[coriben]

Hello,

I'm trying to calculate a simple indicator by dividing one value by another. Consider the following tables:

The first one is the total amount of people in the health plan, per month. The second one is a measure, a filtered value of two certain types of medical apointments, also per month. The third column should simply divide the second table values by the first one. The grand totals are for 36 months, but only four are visible due to cropping.

Now is the weird thing: the division result for the grand totals is right. Only the division by month is distorted, as you can see calculating 201803 for example: 2408/5795 = 0,41.

I don't know what is happenning. For reference here are the DAX queries I'm using to divide the values:

Total Appointments = 
Var Filter = FILTER('Events';'Events'[Type]="Appointment" && 'Events'[Procedure]="1" || 'Events'[Procedure]="2")
Return COUNTROWS(Filter)

Indicator = DIVIDE('Measures'[Total Appointments];COUNT('Users'[ID]))

Thanks in advance!

[v-yulgu-msft]

Hi @coriben,

Is the date field from a separate date dimention table? If not, which table does it belong to?

Please try this:

Total User =
CALCULATE ( COUNT ( 'Users'[ID] ), ALLEXCEPT ( 'Date', 'Date'[dates] ) )

Indicator = DIVIDE('Measures'[Total Appointments],[Total User])

Besides, please post some dummy data of source table (not the calculated values from visual) and make the relationship between each table more clear.

Regards,

Yuliana Gu

[coriben]

Hello @v-yulgu-msft, thanks for your response!

Is the date field from a separate date dimention table? If not, which table does it belong to?

No, it's both in the "Events" and in the "Users" tables, in this case I'm using the date in the Events table, but using the one in the Users table does not work also. I've tried to create a Dates table, but it didn't work as well.

Please try this:

Tried, same result as the screenshot.

Besides, please post some dummy data of source table (not the calculated values from visual)...

Sorry, could not follow you here. How do I do that? What do you mean by calculated values from visual?

[...] make the relationship between each table more clear.

All events in the "Events" table have a "User ID" that is linked with a both ways one to many relationship with the "Users" table User ID.

Thanks a lot for your help so far!

[v-yulgu-msft]

Hi @coriben,

It would be better you can share sample in data view rather than report view. Please have a look at this blog.

Regards,

Yuliana Gu

[coriben · Accepted solution]

Hello, and sorry for the late reply.

Indeed, it was a relationship problem.

For anyone coming to this post in the future: check for disabled relationships whenever your measures are not returning the correct calculations.

In my case, I was trying to link tables in a relationship that would result in a circular reference, so it automatically disabled it.