cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Microsoft
Microsoft

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

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.

Microsoft
Microsoft

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

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!

Microsoft
Microsoft

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors