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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
soshaughnessy7
Frequent Visitor

Dynamic Categorization of data based on measures

Hello All,

 

I'm trying to determine the cheapest price paid for each item and then based on certain criteria bucket the potential savings into different categories.  I've been able to get most of everything I need, however I can only get the categorization to work at the lowest level and not when the data is aggregated.  I know I'm missing something very basic but I can't for the life of me figure it out.  Any help would be immensley appreciated.  

 

I put together a sanitized and simplistic example of the actual dataset.  

 

We begin with this basic structure

soshaughnessy7_0-1622746857069.png

 

I use a measure to determine the lowest price for each item - @MFelix was awesome enough to help with

 

Unit Price Best = MINX(FILTER(ALLSELECTED(Sheet1),Sheet1[INTERNAL_ITEM_CODE]=SELECTEDVALUE(Sheet1[INTERNAL_ITEM_CODE])),Sheet1[Unit Price])

 

Then this measure determines how much we would have theoretically saved had we purchased at the lowest price

Unit Price Opportunity =
var UP = [Unit Price Best]
return
IF(HASONEFILTER(Sheet1[INTERNAL_ITEM_CODE]),
CALCULATE(SUMX(Sheet1,Sheet1[Total]-(Sheet1[Quantity]*UP))),
CALCULATE(SUM(Sheet1[Total])-[Unit Price Best Total])
)
 

This measure determines who the lowest priced supplier is for each item

Unit Price Best Supplier =
var best = [Unit Price Best]
RETURN
CALCULATE(LASTNONBLANK(Sheet1[SUPPLIER_NAME],Sheet1[SUPPLIER_NAME]),Sheet1[Unit Price]=best,ALLEXCEPT(Sheet1,Sheet1[INTERNAL_ITEM_CODE]),ALL(Sheet1[SUPPLIER_NAME]))
 

All of this is working well, however if there's a better way to do any of that feel free to let me know.  The problem comes in with the final measure which attempts to bucket the potential opportunity.  If the item is cheaper from a different supplier it would say other vendor and if we're simply getting the item cheaper from the same vendor it would say Price Parity

 

Unit Price Best Opportunity Type 2 =
var best = [Unit Price Best]
var bs = [Unit Price Best Supplier]

RETURN

SWITCH(TRUE(),
SELECTEDVALUE(Sheet1[Unit Price])=[Unit Price Best],"",
 
SELECTEDVALUE(Sheet1[SUPPLIER_NAME])=bs&&SELECTEDVALUE(Sheet1[Unit Price])>best,"Price Parity",
SELECTEDVALUE(Sheet1[SUPPLIER_NAME])<>bs&&SELECTEDVALUE(Sheet1[Unit Price])>best,"Other Vendor",
"Other")

 

 

When I include the unit price field in the table it works with no issue, however when I roll it up it doesn't.  

 

soshaughnessy7_2-1622747428014.png

 

I need to be able to show it at the level of the middle table as well as simply have a chart that shows the total opportunity by type.  

 

I have a PBIX setup to try and help anyone that would like to help out and will try to determine how to attach it.  

4 REPLIES 4
soshaughnessy7
Frequent Visitor

Thank you both so much, hopefully this link should work.

 

I added a table entitled Expected, which simply summarizes the main table and adds the measures stated above.  Then added an expected sheet for how the expected result should work.  This would of course work for my real dataset, however based on what filters the user selects the lowest unit price may change, thus using a static intermediary table wouldn't work.  I've tried that table as a variable within the measures but just couldn't get it to work.  

 

One last note, the first table on the expected sheet will be a graph on the final report, but as i'm sure you know using measures for your axis labels is a whole other item.  I have that piece working fine in the final solution so for simplicity I kept this pbix to the root issue.  

 

https://drive.google.com/file/d/1JopKLP6A6TWiQhjUH3_uno6qtoGA0j0g/view?usp=sharing

I have still not understood your requirement.  Someone else will help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MFelix
Super User
Super User

Hi @soshaughnessy7 

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Ashish_Mathur
Super User
Super User

Hi

I'd like to try.  Share your PBI file,  In that file, please show the expected result very clearly with a proper explanation.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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