Relative newbie to PowerBI and I'm having trouble using visualisations to present my data in a particular way.
In one Data table I have:
- A Master Data File containing data pertaining to individual products (Number / description / Hierarchy etc.)
In another table I have:
- Sales Data ( Product / Month / Sales in unit QTY & Sales in Monetary Value)
I'm looking to present the data so that I can highlight:
- Number of Products linked to a particular Hierarchy (this part is fine)
- Value of sales by Month for a particular Hierarchy (aslo fine)
but here's where I'm struggling. I also want to show:
- Number of products that make up less than x% of the total value for a particular hierarchy for a given month. For example, if I have 6,000 products in hierarchy 'P', and the turnover for hierachy 'P' in July 2017 was 15million, how many products contributed to just 5% or less of this overall value.
- Or, If I have 6000 products in Hierarchy 'P', how many products had no sales at all in July 2017 (given that they will not appear in the Sales Data Table for this month.
I have all the raw data that I believe I need, but I just can't figure out how to model it - any help would be very much appreciated! The Matrix visualisation looks good to me at a very high level, but it's the drilldown to specifics as highlighted above that I can't get to.
Hi @mclougb6, from a high level modeling perspective, I'd do it this way:
Given the calculation context is one month and one hierarchy :
Compute the total sale for the hierarchy for the month, call this T
For each product in the hierarchy, compute the per-product sum of sales for that month ... call this P
Additionally, for products that have no sales in that month, assign them the value of 0 for P
Then rank these P values into ascending order - ie the worst-performing products are ranked first
Next, compute a running sum for each product, by adding up all the P values for the current product and all the other products that are ranked earlier ... call this R
Now we can compute the bottom-N% position of each product: N = R / T
All products that are in the bottom 5% of the montlhly hierarchy sale will have N values <= 0.05
Finally, you can count the number of these products per hierarchy per month.
Some DAX gurus in this forum can probably write out a single measure that's shorter than all this
thanks for your reply. your high level model is exactly what I'm looking to do ... I just don't know how to translate this into a PowerBI measure to give the desired output.
Essentially All I have are by data tables and the relationships between them connected - but the formulae / DAX wording is brand new.
Hoping someone can write out the measure for me / step by step what I need to do to model the raw data appropriately.
Hi @mclougb6, just to clarify your requirements.... what final outcome do you want exactly? Just the count of products as you mentioned, or would you like to identify the products by name? Or do you want to show the per-product sum of sales as well?
Hi @DAX0110 - it's the count of products within the hierarchy that have reached particular thresholds of sales (be able to split the number of products in the hierachy based on how well they're performing as a percentage of the overall hierarchy sales value for a given month) - for actual sales data for the individual SKUs I have the raw data, so it's not required in my Power BI dash
Do you want a separate count for products that have no sales record at all for the month? Or is it OK to include it in the count of bottom-5% products?
Finally, can you take a screenshot of the relationship view of your data model and post it?
I'd like SKUs with no sales to have their own count / split out from those on < 5%
So the relationship between the two data tables is a one to many ('Master Data'[Material Number] to 'Sales'[Material Number]). I do have a third data table which is mapping for my product groupings. the rationship is many to one ('Master Data'[hierarchy 1] to 'Hierarchy'[Hierarchy 1) as the grouping I want is further down the hierarchy than then Main master data file drills into.
hope that makes sense?