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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gwaczoka23
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
gwaczoka23
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

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.

lbendlin
Super User
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.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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

 

gwaczoka23_4-1661063377704.png

 

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

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"))

vzhangti_0-1661157068213.png

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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