cancel
Showing results for
Did you mean:
Frequent Visitor

## Multiple target KPI for supplier purchase rebate calculation

Hi to all,

I'm searching in the last days for a solution for a problem that i have related to some dashboard for a purchasing department.

The requirement is to create a visual indicatior (KPI) to control the rebate from different suppliers or brands.

For example, a have a supplier A, that was accorded the following:

Purchases

> 500 000,00 € - rebate 0,50 %

> 750 000,00 € - rebate 0,75 %

> 1 000 000,00 € - rebate 1,00 %

I need to know create a visual indicator that when i choose the supplier A it shows the step achived, or not, the amount required to the next step and the rebate amout calculated if any of the steps is achieved.

I hope i made my self clear, but i don't have any ideia how to implement this is PowerBI.

1 ACCEPTED SOLUTION
Community Support

Hi, @SergioSilva

According to your description,I create a model,then calculate three measures to calculate the desired result.

Like this:

``````Achieved =
VAR a =
SUMX (
FILTER ( ALL ( Table1 ), [Country] = SELECTEDVALUE ( Table1[Country] ) ),
[Amount]
)
VAR b =
CALCULATE (
MAX ( Table2[Target] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] < a
)
)
RETURN
IF (
b = BLANK (),
0,
CALCULATE (
MAX ( Table2[Rebate] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] = b
)
)
)``````
``````Atual rebate =
VAR a =
SUMX (
FILTER ( ALL ( Table1 ), [Country] = SELECTEDVALUE ( Table1[Country] ) ),
[Amount]
)
VAR b =
CALCULATE (
MAX ( Table2[Target] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] < a
)
)
VAR c =
IF (
b = BLANK (),
0,
CALCULATE (
MAX ( Table2[Rebate] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] = b
)
)
)
RETURN
a * c``````
``````Missing for next level =
VAR a =
SUMX (
FILTER ( ALL ( Table1 ), [Country] = SELECTEDVALUE ( Table1[Country] ) ),
[Amount]
)
VAR b =
CALCULATE (
MAX ( Table2[Target] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] < a
)
)
VAR c =
CALCULATE (
MIN ( Table2[Target] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] > a
)
)
RETURN
IF ( c - a > 0, c - a, 0 )``````

Here is my sample .pbix file.Hope it helps.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6
Frequent Visitor

@v-janeyg-msft thank for all the help, but the data set and the restrictions on the rebates are way more complicated. Any way, I choose your anwser as solution, because it solved the problem as i explained initially.

Frequent Visitor

@v-janeyg-msft  thank for the solution.

I'm having some trouble to implement it to the atual project.

First the table with purchases have amount for all the years, but this can only have values for 2020, i tried to change the query for the following but withour success:

``````VAR a =
CALCULATE (SUMX (
FILTER ( ALL ( Compras ), [NumContribuinte] = SELECTEDVALUE ( Compras[NumContribuinte] )&&SELECTEDVALUE(Compras[DataDoc],YEAR(TODAY()))),
[Valor]
))``````

Also, when the target is 0, that corresponds to no target, it doesn't calculate correctly.

Sorry for the inconvenience, but is it possible to manage this?

Community Support

Hi, @SergioSilva

Obviously there is a problem with your formula, but if I don’t know how your table structure is, what fields and data types it has, it will be difficult for me to help you correct it.

Could you share some sample data with fake data?So we can help you soon.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi, @SergioSilva

According to your description,I create a model,then calculate three measures to calculate the desired result.

Like this:

``````Achieved =
VAR a =
SUMX (
FILTER ( ALL ( Table1 ), [Country] = SELECTEDVALUE ( Table1[Country] ) ),
[Amount]
)
VAR b =
CALCULATE (
MAX ( Table2[Target] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] < a
)
)
RETURN
IF (
b = BLANK (),
0,
CALCULATE (
MAX ( Table2[Rebate] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] = b
)
)
)``````
``````Atual rebate =
VAR a =
SUMX (
FILTER ( ALL ( Table1 ), [Country] = SELECTEDVALUE ( Table1[Country] ) ),
[Amount]
)
VAR b =
CALCULATE (
MAX ( Table2[Target] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] < a
)
)
VAR c =
IF (
b = BLANK (),
0,
CALCULATE (
MAX ( Table2[Rebate] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] = b
)
)
)
RETURN
a * c``````
``````Missing for next level =
VAR a =
SUMX (
FILTER ( ALL ( Table1 ), [Country] = SELECTEDVALUE ( Table1[Country] ) ),
[Amount]
)
VAR b =
CALCULATE (
MAX ( Table2[Target] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] < a
)
)
VAR c =
CALCULATE (
MIN ( Table2[Target] ),
FILTER (
ALL ( Table2 ),
[Country] = SELECTEDVALUE ( Table1[Country] )
&& [Target] > a
)
)
RETURN
IF ( c - a > 0, c - a, 0 )``````

Here is my sample .pbix file.Hope it helps.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Here is the file with sample data sample_data.xlsx.

This table has the purchases along the year for each brand:

Then i have another table with the rebate agreements:

In the end the idea is to have a visual control per brand of the rebate achieved and the amount required for the next target if applicable. Example.

It doesn't have to be exacly like this, as i'm open to sugestions.

Thanks once again!

Community Support

Hi, @SergioSilva

It’s my pleasure to answer for you.

According to your description,I think I understand what you need.Could you mind providing some sample data and your expected result image with onedrive ,so we can help you soon.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements