Question with measure

[Anonymous]

Hi All,

I'm going to try to explain my situation because is a little bit complicate xD.

I have a "master" excel file with some data like this:

In this excel we have some columns like location code, name of location and the rest of the columns are sales amount and % of

discount depending of the quantity of the sales amount.

Then I have a dashboard with a table like this:

In this table yellow is name of client , red is sales amout and blue is the %discont depending the total sales amount.

The question is, I want to change one percentaje in excel file and automactly applys to this table. The measure that I'm using to extract the % is:

Banda = 

VAR _banda1 = MAX(Farmacias[banda1])
VAR _banda2 = MAX(Farmacias[banda2])
VAR _banda3 = MAX(Farmacias[banda3])
VAR _banda4 = MAX(Farmacias[banda4])
VAR _banda5 = MAX(Farmacias[banda5])
VAR _banda1a = MAX(Farmacias[%banda1])
VAR _banda2a = MAX(Farmacias[%banda2])
VAR _banda3a = MAX(Farmacias[%banda3])
VAR _banda4a = MAX(Farmacias[%banda4])
VAR _banda5a = MAX(Farmacias[%banda5])
VAR _banda6a = MAX(Farmacias[%banda6])
RETURN

SWITCH(TRUE();
             ([sumcantidad]> 0 && [sumcantidad]<=_banda1); _banda1a;
             ([sumcantidad]>_banda1 && [sumcantidad]<=_banda2); _banda2a;
             ([sumcantidad]>_banda2 && [sumcantidad]<=_banda3); _banda3a;
             ([sumcantidad]>_banda3 && [sumcantidad]<=_banda4); _banda4a;
             ([sumcantidad]>_banda4 && [sumcantidad]<=_banda5); _banda5a;
             ([sumcantidad]>_banda5); _banda6a;
            0
            )

I think, the problem here is the "MAX" of the variable, because for example if I try to change a % in the excel file depending the  of the number with MAX is not applying the change. I don't know if is possible to access directy to a row, not to column.

Sorry for the explanation, really complicate for me explain this topic 😞

[Anonymous]

Hi,

some questions before answering:

when you speak about changing a percentage, you mean changing the value of a single cell in the excel?

what is the wrong result when you refresh your report?

can you share the original value and the new value you want to use?

[Anonymous]

Hi @Anonymous 

when you speak about changing a percentage, you mean changing the value of a single cell in the excel?

Yes, exactly that. Because in some cases the final user can apply different discounts in different clients only applying a modification of the value in the excel.

what is the wrong result when you refresh your report?

can you share the original value and the new value you want to use?

maybe with and example is easy to understand:

cuantity more that 4000, then the discount is -30% but for an specific client I want to apply a -35% of discount. Then with the measure that I have, still appearing -30%. I think is for the MAX function but i don't know.

[Anonymous]

I try to replicate your data model, using your measures.

When I modify last %banda for one farmacia in my source excel, save it and refresh PowerBI, it works fine.

I suppose that in Farmacias table, you have only one row for each Farmacia, is it correct?

What is data type of %bandaN in your model?

[Anonymous]

I suppose that in Farmacias table, you have only one row for each Farmacia, is it correct?

No , I have more than one row for each farmacia.

Important topic:

I have othe measure that is the sum of all "Cantidad" for each farmacia.

What is data type of %bandaN in your model?

To take in account the column %bandaN is in other table (At the end is the excel file) and the data type is Percentage. The table in Power Bi looks like this.

In this table there is only one row for each farmacia.

[Anonymous]

Hi @Anonymous any tip?