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
mclougb6
Frequent Visitor

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

 

 

11 REPLIES 11
DAX0110
Resolver V
Resolver V

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 😉

 

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

 

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?

 

 

Hi @DAX0110,

 

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?

 

 

What I need to know is the names of all the relevant columns - eg, the sales amount column - the relationship view should show these names

 

 

If you can supply a small sample dataset, so that I can test the formulas before sending them to you, that would be ideal.

Hi @DAX0110

 

Have sent you a private messabge with more data / info.

Hi @mclougb6,

 

I'm assuming that your visual contains two filtering dimensions:  month, and hierarchy (no products).  The following measures will calculate two numbers for each hierarchy: 

  1. number of products with no sales in that month, and
  2. number of products whose collective sales account for 5% or less in that month

This is a draft solution that's tested over my own data model, so some adjustments may be required to adapt to yours.

 

And a final heads-up: these measures could take some time to calculate, depending on the size of your data.

 

Please create three measures :

 

 

Total Sales := SUM( Sales[Net Revenue] )

 

 

Count of Products With No Sales :=
    /* per month and per hierarchy (category) */
VAR countOfProducts =
    SUMX(
        VALUES( 'Master Data'[Material Number] )
        , IF( ISBLANK([Total Sales]), 1, 0 )
        )
RETURN
    countOfProducts

 

 

Count of Products in Bottom 5% :=
    /* per month and per hierarchy (category) */
    
VAR threshold = 0.05

VAR monthlyHierarchyTotalSales = [Total Sales]

VAR amountThreshold = monthlyHierarchyTotalSales * threshold

VAR countOfProducts =
    SUMX(
        VALUES('Master Data'[Material Number])
        , 
            VAR thisProductSales = [Total Sales]
            VAR lesserProductsSales = 
                SUMX(
                    VALUES('Master Data'[Material Number])
                    , 
                        VAR eachProductSales = [Total Sales]
                        RETURN    
                            IF( eachProductSales < thisProductSales
                                , eachProductSales
                                , 0
                                )
                    )
            VAR runningSales = lesserProductsSales + thisProductSales
            RETURN
                IF( ISBLANK(thisProductSales)
                    , 0
                    , IF( runningSales > amountThreshold, 0, 1 )
                    )
        )
                
RETURN
    countOfProducts

 

 

Hi @DAX0110,

 

Measure 1 - total sales is working perfectly fine for each of the product hierarchies 🙂

 

Measusure 2 - is causing some issues. It's only counting the Products that appear on the Sales Data Table for a given month that have 'blank' revenue. I'm also looking for, in this measure, the count of the products on the 'Master Data' tab that do not appear on the sales data table at all for a given month (Our sales reporting system will only show products that have had activity against them for the month).

 

For example, Hierarchy X has 8000 Products (Master Data) - In July we had sales against 3000 of these products and no sales data in this month for 5000 products. However, while we've not had any positive revenue against 5000 products, the sales report is showing 3100 of the products against this hierarchy for July. 3000 have a positive revenue (due to sales), 38 have negative revenue (due to returns), and 62 have 'blank' in the revenue field (but appear on the file due to financial corrections affecting margin).

 

Measure 2 is returning just the 62 products as 'Count of Products with no sales'. Ideally I would like this measure to also include the 38 products with negative revenue (as technically these products also had no sales) but more importantly I want this measure to include the 4900 additional products that are linked to hierarchy X in the master data table, but which did not appear at all in the sales report for July.

 

Measure 3 is also returning an incorrect value, but I can't quite figure out where it's going wrong. For this value, what I'm looking for is the following. If Net Revenue in July for hierarchy X is 1,000,000, out of the products appearing in the sales report for July, (some of these products will have a negative revenue and some will show blank in this field, as well as all those with actual prositive revenue) how many of them make up just 5% of the total revenue. Is this what you've written the measure to produce?

 

Appreciate all the help on this!

B

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.