Same formula doesn't work in different files

[jlarques]

Dear community,

I have a formula in a file created two years ago that works perfectly. This formula is based in a table range that users can choose the billing range. It depends on the user's choice, I compare the table range with from and to with my formula, and I calculate sales.

The table range contains:

Range                                                       From             To

Until 60.000€                                                     0€           60.000€

From 60.000€ to 9.000€                            60.000€           90.000€

From 90.000€ to 120.000€                        90.000€         120.000€ 

The range column is related to the data filter, and when user chooses Until 60.000€, the formula calculates sales only in this range.My formula is:

Range by Country=

IF(SELECTEDVALUE(RANGE[Range]=BLANK();
    SUMX(FILTER(VALUES([MAESTRO PAISES]PAIS); Total Sales>=0 && Total Sales<=60000; Total Sales);
    SUMX(FILTER(VALUES([MAESTRO PAISES]PAIS); Total Sales>=RANGE[From] && Total Sales<=RANGE[To];Total Sales)

)

This formula works in a file created in 2018. I have created the same in a new file with the same data source(Excel file) and the formula doesn't work. I reviewed everything(relationships, data types, data format, etc.) but the formula doesn't work. Do you know where it can be the issue? Could it be an issue from the version created before?

Thanks to everyone.

[MFelix · Accepted solution]

Hi @jlarques ,

Do you get any error message on the visualizations or just blank values?

[jlarques]

Hi Miguel,

your answer is working very well! Thanks for all your support and help!

Kind regards,

José Luis

[jlarques]

Hi @MFelix ,

I have only blanks.

Regards,

José Luis 

[MFelix]

Is it possible to share a sample file?

[jlarques]

For sure. 

Please, check Page 6. The first matrix on the left side is by country and the second one is by item. With the second one I use the same formula, but by item. It seems that works but not for all ranges, because you can see that I choose from 90.000€ to 120.000€ and Malvidas appears with 234.000€ and it wouldn't have to appear, because it is not this range.

How can I share the file? I don't any option to add it here. Do I need to share it by the cloud?

Thanks for your help, @MFelix 

Regards,

[MFelix · Accepted solution]

Hi @jlarques ,

Based on the file you have send out try to change you measure to:

TestOne = 
CALCULATE (
    SUMX (
        SUMMARIZE ( PEDIDOS; 'MAESTRO PAISES'[PAÍS]; CALENDARIO[AÑO]; PEDIDOS[TOTAL] );
         PEDIDOS[TOTAL] 
    );
    FILTER (
        ALL ( PEDIDOS[TOTAL] );
        PEDIDOS[TOTAL] >= VALUES ( RANGO[DESDE] )
            && PEDIDOS[TOTAL] <= VALUES ( RANGO[HASTA] )
    )
)

Believe that your issue is related with the context of the measure that needs to be well created in this case using the MAESTRO and CALENDARIO table alonside with PEDIDOS.

Regarding the question about the 2018 setup I could only give you the answer if I was abble to compare both files, can be a context or filtering issue.

Hope this helps.

[MFelix]

Share trough onedrive, google drive, we transfer or similar link

[MFelix · Accepted solution]

Hi @jlarques ,

Do you get any error message on the visualizations or just blank values?