DIVIDE DAX with error when using 2 numerators

[Anonymous]

Hi, I'm trying to calculate a percentage index of goal/accomplished.

But the divide function is causing defect in matrix display when using 2 numerators in the divide function.

For a reason I don't know, when use 2 numerators in divide formula, the page filter "year" doesn't works. The matrix show me all dates avaiable in my date dimension table.

The formula is:

DIVIDE (number1 - constant, number2 - constant)

Where the constant is represented by the measure [LINHA BASE MARG] with value 0.25

See:

When I use only one numerator, it works as expected (except by the fact that I have the wrong result because is lacking one value in numerator divide formula):

Any solution?

[aj1973]

Hi @Anonymous 

add VAR for each equation then use them in Divide:

var _1 = A-B

var _2 = C-D

var _Divide = DIVIDE( _1 ,_ 2 , 0)

RETURN

_Divide

or use _Result = (A-B) / (C-D)

[Anonymous]

The issue persist:

[aj1973]

Can you verify and RETURN 

ARG1

ARG2

and show the result please?

[Anonymous]

Arg1 and Arg2 below:

 

There isn't problem with the arg values.

I've found if I set the year filter in visual, it works. But I already have a year filter defined in the page filters.

[aj1973]

From your visual I can see ARG1 < 0 and ARG2 > 0 when you divide them the result ICM is always < 0 .

In your IF statment 

you wanted to RETURN 0 if ICM < 0. That<s why you see 0 in your visual.

[Anonymous]

The zero is not the problem, show zero to years 2016, 2017, 2018, etc when I have a filter defined to 2021 is the problem. I don't wanna see values to other years.

Here a similar discussing: https://community.powerbi.com/t5/Desktop/Slicer-not-filtering-measure-with-constant-value-over-date-...

[aj1973]

Then you need to use SELECTEDVALUE for all your measures involved in DIVIDE.

May be if you can share your file I can help you better.

[Anonymous]

Link to download the pbi file: https://we.tl/t-t7fKRwLDjP

[aj1973]

Here are the mistakes:

1- in your Matrix you are using fields that have inactive relationship with each others like using DATA FISCAL with SUPERMETA 2021

2- The inactive relationship between Tables are Many to Many (Wrong)
3- You see 0% for all years but 2021, because in your tabel "SUPERMETA 2021" you don't have data from the other years

4- most of the measures if not all where you are using USERELATIONSHIP are not correct since the relationship is Many to Many

5- The model (Tables and relationships) is not good. There is no star schema in place, Data fiscal....

6- In your model i see your using 3 Tables for your visuals, why uploading the rest of the Tables if you are not going to use them!

I recommend making some order in your file before going any further...

[Anonymous]

@aj1973 the page "SUPERMETA FAT 2021" works with same tables and works like a charm. There isn't problem with the relationship between the data.

The issue only occurs when a CONSTANT (fixed number) is added to the numerator of divide of my measure in page "SUPERMETA MARG 2021".

Like I said in description, when it doesn't have the constant in formula, everything goes well, but I need that constant, so I cant left the formula without it.

[v-kelly-msft]

Hi @Anonymous ,

After checking your sample data,while using the 2 formula below:

ICM MARG1 = 
var ARG1=[% MARGEM SM]
VAR ARG2 = [META MARG] - [LINHA BASE MARG]
VAR ICM = DIVIDE(ARG1, ARG2) //verificar porque quando o numerador do divide tem um número sendo subtraído a matriz perde o filtro do ano
VAR RESUL = IF(ICM < 0, 0, ICM)
RETURN RESUL

ICM MARG = 
VAR _ARG1 = [% MARGEM SM] - [LINHA BASE MARG]
VAR ARG2 = [META MARG] - [LINHA BASE MARG]
VAR ICM = DIVIDE(_ARG1, ARG2) //verificar porque quando o numerador do divide tem um número sendo subtraído a matriz perde o filtro do ano
VAR RESUL = IF(ICM < 0, 0, ICM)
RETURN RESUL

When you move the slicer of matrix to the same year.They both have data:

So where is the problem?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft . I have a page filter defined to 2021 and the matrix show values to years 2016, 2017, 2018, etc, when I put a constant ( [LINHA BASE MARG] = 0.25 ) in numerator of divide. I don't wanna see values to other years.

I can't understand why the page filter doesn't works when I have a constant in numerator of divide. 🤷‍♂️

If the constant doesn't be there in numerator of divide, the page filter works decently.

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

I see.

If you return ICM,you will find that the measure without constant value still showing result only for 2021:

But the measure with constant value are showing values with all the years:

It means that the if condition doesn work in your measure,so try to modify your measure as below:

ICM MARG = 
VAR _ARG1 = [% MARGEM SM] - [LINHA BASE MARG]
VAR ARG2 = [META MARG] - [LINHA BASE MARG]
VAR ICM = DIVIDE(_ARG1, ARG2) //verificar porque quando o numerador do divide tem um número sendo subtraído a matriz perde o filtro do ano
VAR RESUL = IF(ICM < 0, BLANK(), ICM)
RETURN  RESUL

And you will see:

The result only contains the value in 2021.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Awesome! Thank you!

[aj1973]

My freind this is your Matrix without Values

Please don't tell me that there is nothing wrong.

[Anonymous]

NEWS:
my measure use a constant value, then it is going to show for all dates regardless how I filter it because the measure is always returning a value. But, how to solve this?