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
Anonymous
Not applicable

grouping inventory classifications - circular reference errors

Hi there,

 

I'm trying to get group/ classify my numbers into several groups, based on the values per record/ row. 

I have 3 tables:

  • 1 containing inventory levels per sku
  • 2 containing sales per sku
  • 3 list of all SKU's (unique). 

these 2 tables are linked by SKU via a 3rd table, which contains all SKU's as unique value. 

I've created a measure to define the rolling 12 months sales from table 2. (meaning, per sku I'll have the sales over the 12 last months, up until today). this measure does what it's meant to do. 

I then created a measure dividing the inventory level per sku by this 12month's rolling sales value. This way I get per sku how many years it will take to deplete the inventory levels. I've called this Inventory Coverage. 

All good so far. 

What I need is a way to group the outcomes of this "Inventory Coverage" into a couple of 'buckets'. 

0-0.5

0.5-1

1-2

2-4

>4

I tried using a summarize function in which I used switch functionality. This by itself worked fine, yet when I tried to link this created table back to my model, using the SKU, I keep getting circular reference errors and I can't seem to find a way around this. 

 

any suggestions, by whichever means are most welcome from the community. 

 

cheers!

 

 

 

 

 

 

1 ACCEPTED SOLUTION
lc_finance
Solution Sage
Solution Sage

Hi @Anonymous ,

 

 

thank you for the interesting question.

 

In order to best help you, can you share:

1) a Power BI sample? You can share it using One Drive, Google Drive or another similar tool

2) an example of the visualizations you want to have. To figure out a workaround for the circular reference, the best is to know what is your goal in terms of visualizations

 

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

View solution in original post

8 REPLIES 8
lc_finance
Solution Sage
Solution Sage

Hi @Anonymous ,

 

 

thank you for the interesting question.

 

In order to best help you, can you share:

1) a Power BI sample? You can share it using One Drive, Google Drive or another similar tool

2) an example of the visualizations you want to have. To figure out a workaround for the circular reference, the best is to know what is your goal in terms of visualizations

 

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

Anonymous
Not applicable

secondly @lc_finance ,  I'm not sure you'll have access to this and I've not figured out how to grant this forum access to my google drive account. 

Anonymous
Not applicable

You need to share the file as a public file.

This link should help you out: https://support.google.com/drive/answer/2494822?co=GENIE.Platform%3DDesktop&hl=en

Anonymous
Not applicable

dear @lc_finance ,

 

thanks for your feedback. I've placed a dummy model on below link from google drive. it's based on simplified excel table sources, but the principle is the same I'm using in my larger model and where I'm stuck at. 

 

https://drive.google.com/drive/folders/1YWsVAB-gpqZAruQSRSIP2Oe4XLEAJbk6 

 

The output of the inventory divided by the sales works well per sku. What I'm looking for is a way to group these values per sku in the buckets (as example) shown on the x-axis. 

 

hope this clarifies. 

regards,

 

 

example of output in graph.pngexample of output in table.png

Hi @Anonymous ,

 

 

you can download my proposed solution from here.

 

I included the classification formulas directly in the inventory table, and it works well. Below is a screenshot:

INVMGMT solution.png

 

And here is the DAX formula:

CovClassR12 = SWITCH(
    TRUE,
    [OH_Cov_R12] <= 1/6, "1: 0-2 months",
    [OH_Cov_R12] <= 0.5, "2: 2-6 months",
    [OH_Cov_R12] <= 1, "3: 6-12 months",
    [OH_Cov_R12] <= 1.5, "4: 12-18 months",
    [OH_Cov_R12] <= 2, "5: 18-24 months",
    [OH_Cov_R12] <= 4, "6: 2-4 years",
    "7: > 4 years"    
)

 

Does this help you?

 

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

 

Anonymous
Not applicable

thanks @lc_finance ,

 

this looks to be a good alternative. through other means I'd found a solution by now. I'd created a manual reference table with the classifications I'd wanted and then added the individual values to the SKU table. This works as well and gives me the result I was after. 

thanks for the effort and suggestion. 

 

 

Hi @Anonymous ,

 

 

I am glad you were able to find a solution.

Do not hesitate if you need more help,

 

LC

If you check out the following pattern https://www.daxpatterns.com/dynamic-segmentation/

 

There is a heading part way down the article about segmenting by a measure. This should illustrate a technique to do what you want.

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.