cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SergioSilva
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
v-janeyg-msft
Community Support
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 )

1.png

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

If it doesn’t solve your problem, please feel free to ask me.

 

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.

View solution in original post

6 REPLIES 6
SergioSilva
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. 

SergioSilva
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?

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.

v-janeyg-msft
Community Support
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 )

1.png

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

If it doesn’t solve your problem, please feel free to ask me.

 

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.

View solution in original post

SergioSilva
Frequent Visitor

Hi @v-janeyg-msft,

 

Thanks for the reply and your help.

 

Here is the file with sample data sample_data.xlsx.

 

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

compras.jpg

 

Then i have another table with the rebate agreements:

acordos_marcas.jpg

 

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.

visual_kpi.jpg

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

 

Thanks once again!

v-janeyg-msft
Community Support
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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.