Forum Discussion

mclougb6's avatar
mclougb6
Frequent Visitor
8 years ago

Issue Presenting Data

Hi all,

 

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.

 

thanks!

B

 

 

Need Help
Tutorial Requests

11 Replies

  • DAX0110's avatar
    DAX0110
    Resolver V
    8 years ago

    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 ;)

     

    • mclougb6's avatar
      mclougb6
      Frequent Visitor
      8 years ago

      Hi Dax0110,

       

      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.

       

      thanks,

      B

       

      • DAX0110's avatar
        DAX0110
        Resolver V
        8 years ago

        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?