Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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, @Anonymous 

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
Anonymous
Not applicable

@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. 

Anonymous
Not applicable

@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, @Anonymous 

 

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, @Anonymous 

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.

Anonymous
Not applicable

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, @Anonymous 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.