topic Re: Incorrect result when dividing with counted columns in DAX Commands and Tips

Incorrect result when dividing with counted columns

Clout — Tue, 06 Apr 2021 20:19:18 GMT

Hello guys,

I have this DAX Function as a calculated column :

Spalte = IF(ISBLANK(Frachten[AK_C_P]), BLANK(),

DIVIDE(COUNTROWS(FILTER(Frachten, Frachten[AK_C_P] <= MAX('Global'[GW_AblaufPges]))), COUNT(Frachten[AK_C_P])))

So I want to check if "AK_C_CSB" is <= "GW_AblaufCSB" and count the rows where the condition is fulfilled. And then I want to count every row in "AK_C_CSB" which is not null. So the next step is to determine the ratio between the first count and the second count by dividing them together. And then I want to create a card visual where the average of the ratio is shown in my setted slicer time intervall.

My data structure looks like this:

Table "Frachten:

Table "Global"

And this is the result as a table:

And when I want the average of the ratio per month, I get the same result with the value 4,86 for every month.

The most months should be shown the ratio 1 and for a few months the ratio is 0,xx. So the ratio > 1 is impossible.

Here is the file:

Google Drive

Re: Incorrect result when dividing with counted columns

amitchandak — Wed, 07 Apr 2021 05:10:24 GMT

Clout , I think it should be like this measure

DIVIDE(COUNTROWS(FILTER(allselected(Frachten), Frachten[dataum] <= MAX('Global'[dataum]))), calculate(COUNT(Frachten[AK_C_P]),allselected(Frachten)))

Re: Incorrect result when dividing with counted columns

Clout — Wed, 07 Apr 2021 11:33:45 GMT

Hello thank you for your answer,

but I got this solution now:

RM_NH4 = VAR numerator = CALCULATE(COUNTROWS(Frachten),FILTER(Frachten, Frachten[AK_C_NH4] <> Blank() && Frachten[AK_C_NH4] <= Max('Global'[GW_AblaufNH4N]))) VAR denominator = COUNT(Frachten[AK_C_NH4]) RETURN DIVIDE(numerator, denominator)