Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 )
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.
@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.
@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.
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 )
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.
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:
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!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |