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