Issue Presenting Data

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

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

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