Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi to all guys,
I want to ask you if somebody knows a method to generalize a formula without specifying the hierarchy level in the visual.
But first, let's contextualize the problem. The table below contains the data supporting the example.
Let's P0 be a group of products, P001 a specific product, and P001-A and P001-B two different packs of the same product P001. In the column Property we have three properties for that item. The Stock Projections are the expected stocks available at the end of that month. The Sales Forecast value indicates a prediction of the sold stocks in that month, and with Coverage we want to find the number of days that the stock at the end of the current month will be able to cover based on the future Sales Forecasts.
Let's suppose that every month has 22 working days.
Then for example, for P001-A we have 21 days of Coverage since the Stock Projections are 29 items at the end of Jun, and for July the sales forecast is 30 (we can cover almost the full Jul and our formula give us 21 days). For the product P001 we made the same calculation, but taking into account that the stock projections and the sales forecast are the sums of the packs of the specific product.
In this example, we had to construct a support table containing the sum of stock projections and sales forecast to compute all the Coverage values for all the levels over the packs. But we want to avoid that.
Finally, we want to find a general formula giving us the Coverage without specifying the hierarchy level in the visual.
Anyone can help?
Thanks
Product | Property | Jun-22 | Jul-22 | Aug-22 |
P0 | Stock Projections | ... | ... | ... |
P0 | Sales Forecast | ... | ... | |
P0 | Coverage | ... | ... | ... |
P001 | Stock Projections | 139 | 188 | 270 |
P001 | Sales Forecast | 81 | 103 | |
P001 | Coverage | 33 | ... | ... |
P001-A | Stock Projections | 29 | 50 | 30 |
P001-A | Sales Forecast | 30 | 23 | |
P001-A | Coverage | 21 | ... | ... |
P001-B | Stock Projections | 110 | 138 | 240 |
P001-B | Sales Forecast | 51 | 80 | |
P001-B | Coverage | 37 | ... | ... |
Hi @SteGot ,
Are you trying to replace the hierarchy in a visual with a formula? As far as I know, there is no such formula that can replace the hierarchy of visual effects.
If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
108 | |
101 | |
38 | |
35 |
User | Count |
---|---|
149 | |
122 | |
76 | |
74 | |
52 |