cancel
Showing results for
Did you mean:
Frequent Visitor

How to show result of the measure on diagram?

Hi,

I created a pretty complicated (for me😂) measure, which use the flags for the product, for example:

ITEM A has flag 1 when its price is lower than the target price.

The flag is called [Profit Sweetener (1/0)]

1 - when offered price for the product is below the target

0 - when we can't buy it, because the target is not achieved

Reason of PS Flag change =
IF(Suppliers[Profit Sweetener Flag]=Suppliers[PreviousDay Profit Sweetener Flag],
BLANK(),
IF(Suppliers[Profit Sweeteners Flag]>Suppliers[PreviousDay Profit Sweetener Flag],BLANK(),
IF(Suppliers[PreviousDay Target Price]>[Current Target Price],"↓ Target Price ↓","↑ Offered Price ↑")))

Now I want to show on the diagram or in the table how many reasons I have if I filter for the one supplier.
For example:

For suppler1 I have 50 changes because of ↓ Target Price ↓ and  20 because of ↑ Offered Price ↑

Is it possible to do it without making a column in the source table? Becuase to be honest it is hard for me, especially because I have previous date, when it is not a DAY-1, it can be -3, -5 days, depends when I receive the data. So it should use the last date before current and I don't know how to make it

Thanks for help 🙏
5 REPLIES 5
Frequent Visitor

Thank you so much. Unfortunately it's not working. I also don't really understand this measure:

`PreviousDay Target Price = `

I have a table like on your result, but please remember that I would like to have a summarize:

 Supplier Reason - lower target price Reason - higher offered price A 50 20
Community Support

Hi, @gwaczoka23

What was the [PreviousDay Target Price] mentioned in your previous information? Don't see the data you gave?

``````Reason of PS Flag change =
IF(Suppliers[Profit Sweetener Flag]=Suppliers[PreviousDay Profit Sweetener Flag],
BLANK(),
IF(Suppliers[Profit Sweeteners Flag]>Suppliers[PreviousDay Profit Sweetener Flag],BLANK(),
IF(Suppliers[PreviousDay Target Price]>[Current Target Price],"↓ Target Price ↓","↑ Offered Price ↑")))``````

Is it possible to explain this formula? The data you provide does not calculate the results of 50 and 20.

Best Regards,

Community Support Team _Charlotte

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

Super User

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

Frequent Visitor

Hi Ibendlin!

I tried to prepare sample data in Excel,

I use a green color in columns where in Power BI I have this information in measures

Of course there is much more products and suppliers.

I want to show something like that:

On 15.08.2022 for Supplier A we have:

- 200 products which are profit sweetener (column F)

- 5 products which are not profit sweetener anymore (there was in previous date) because of the change in target price

- 10 products which are not profit sweetener anymore because of higher offered net price

A sample is for one product to don't complicate it too much 🙂

My measure to calculate if the product had the flag previously:

PreviousDay Profit Sweetener Flag =
VAR DAY2 = DATEADD(Suppliers[Data cennika],-2,DAY)
VAR DAY3 = DATEADD(Suppliers[Data cennika],-3,DAY)
VAR PSflagDAY2 =
CALCULATE(
Suppliers[Profit Sweeteners (per Supplier)],DAY2
)
VAR PSflagDAY3 =
CALCULATE(
Suppliers[Profit Sweeteners (per Supplier)],DAY3
)
VAR PSflagPREVIOUSDAY=
IF(ISBLANK(PSflagDAY2),PSflagDAY3,PSflagDAY2)
RETURN
PSflagPREVIOUSDAY

I use DAY-2 and DAY-3 because I have data for Mondays, Wednesdays and Fridays so it's almost always a reference to -2 and -3 day
Community Support

Hi, @gwaczoka23

You can try the following methods.

Meausre:

``Profit Sweetener (1/0) = IF(SELECTEDVALUE(Suppliers[Net price])<SELECTEDVALUE(Suppliers[Target Price]),1,0)``
``````PreviousDay Profit Sweetener Flag =
VAR DAY2 = DATEADD(Suppliers[Date],-2,DAY)
VAR DAY3 = DATEADD(Suppliers[Date],-3,DAY)
VAR PSflagDAY2 =
CALCULATE(
Suppliers[Profit Sweetener (1/0)],DAY2
)
VAR PSflagDAY3 =
CALCULATE(
Suppliers[Profit Sweetener (1/0)],DAY3
)
VAR PSflagPREVIOUSDAY=
IF(PSflagDAY2=BLANK(),PSflagDAY3,PSflagDAY2)
RETURN
PSflagPREVIOUSDAY``````
``````PreviousDay Target Price =
Var PrevDate=MAXX(FILTER(ALL(Suppliers[Date]),[Date]<SELECTEDVALUE(Suppliers[Date])),Suppliers[Date])
Var PreviousDayTargetPrice=CALCULATE(SUM(Suppliers[Target Price]),FILTER(ALL(Suppliers),[Date]=PrevDate))
Return
PreviousDayTargetPrice``````
``````Reason of change =
IF(Suppliers[Profit Sweetener (1/0)]>=Suppliers[PreviousDay Profit Sweetener Flag],
BLANK(),
IF([PreviousDay Target Price]>SELECTEDVALUE(Suppliers[Target Price]),"Lower Target Price",
"Higher Target Price"))``````

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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