Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear all,
I am creating a visual for a very specific use case, and I cannot find any posts to guide me through - I am also quite new to PowerBI..
In my data set I have one table with a list of products, each product having one or many components. (Table 1 in the picture below) - I defined the Component ID in a way that they only relate to on product.
The Components have supply and demand numbers per Calendar Week (Table 2).
The demand for the components belonging to one product are the same, since customers demand the product as a whole.
The problem is, that for one product, the amount of Supply depends only on the component with the lowest supply --> meaning I would like to get as an outcome a table with only the product, and the minimum number of component supply per Calendar Week.
I am a bit lost how to do this best. I could go back to Excel and write a Macro that does that, but I would like to learn how to do it in PowerBI!
Thanks a lot for any kind of support!
Cheers!
Solved! Go to Solution.
Hi, @Anonymous
According to your description, you want to get the min value of the Supply and Demand group by Week and Product ID, right? I think you can achieve this using Summarize() function in DAX, you can follow my steps:
Product ID = RELATED('Table 1'[Product ID])
Outcome =
SUMMARIZE(
'Table 2',
[Product ID],[Calendar Week],
"DemandMin",MIN('Table 2'[Demand]),
"SupplyMin",MIN('Table 2'[Supply]))
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
According to your description, you want to get the min value of the Supply and Demand group by Week and Product ID, right? I think you can achieve this using Summarize() function in DAX, you can follow my steps:
Product ID = RELATED('Table 1'[Product ID])
Outcome =
SUMMARIZE(
'Table 2',
[Product ID],[Calendar Week],
"DemandMin",MIN('Table 2'[Demand]),
"SupplyMin",MIN('Table 2'[Supply]))
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
Can you please provide a sample dataset or PBIX (hiding confidential information) and a depiction of the expected outcome?
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown,
Thanks for replying!
Is the screenshot with Table1, Table2 and wished outcome not enough? I created this dummy set with less rows, which looks a lot like mine. I cannot upload anything more specific unfortunately..
@Anonymous
please share this dummy dataset using Onedrive, Google Drive, Dropbox... it avoids all of us having to type in all the data in an excel sheet.
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |