Determining frequency of least profitable products for poor performing projects

[geotech]

Hello All,

I have the following table of data where I am interested in determining for frequency/count of products that yield negative gross margin for projects whose overall gross margin are less than 20%. 

Project name	Product List	Revenue	Gross Margin	GM%
Project 1	Product 1	10000	4000	40%
Project 1	Product 2	250	-1000	-400%
Project 1	Product 3	400	-2000	-500%
Project 2	Product 1	5000	1000	20%
Project 2	Product 2	400	-200	-50%
Project 2	Product 3	100	-500	-500%
Project 2	Product 4	1200	-2500	-208%
Project 3	Product 1	20000	8000	40%
Project 3	Product 2	15000	7000	47%

I've created a measure to determine the GM% for each project, however I'm not sure how I can target these specific projects and display those products with negative GM. In the table above, Projects 1 and 2 would meet the <20% GM criteria and in either a pie chart visual or table visual Products 2, 3, and 4 would yeild counts of 2, 2, and 1, respectively. 

Thank you for any support you can offer.

[v-juanli-msft · Accepted solution]

Hi @geotech 

Create measures

Measure GM % =
CALCULATE (
    SUM ( 'Table'[Gross Margin] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Project name] = MAX ( 'Table'[Project name] )
            && 'Table'[Product List] = MAX ( 'Table'[Product List] )
    )
)
    / CALCULATE (
        SUM ( 'Table'[Revenue] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Project name] = MAX ( 'Table'[Project name] )
                && 'Table'[Product List] = MAX ( 'Table'[Product List] )
        )
    )

SUM GM = SUMX(FILTER(ALLSELECTED('Table'),'Table'[Project name]=MAX('Table'[Project name])),[Measure GM %])

negative =
CALCULATE (
    COUNT ( 'Table'[Product List] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Project name] = MAX ( 'Table'[Project name] )
            && 'Table'[Product List] = MAX ( 'Table'[Product List] )
            && 'Table'[Measure GM %] < 0
    )
)


Counts =
CALCULATE (
    COUNT ( 'Table'[Product List] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[SUM GM] < 0.2
            && 'Table'[Product List] = MAX ( 'Table'[Product List] )
            && [negative] <> BLANK ()
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Would a single visual like this work for you?  You may download my PBI file from here.

[amitchandak]

You need to group data at that level and filter. Please find an example below

Total value = Calculate(
    count(data[employee ID]),
    FILTER(  
        ADDCOLUMNS( 
            SUMMARIZE(
                data,
                data[employee ID],
				"sum_exp",
				Sum( 'Date'[expense]),
                "Max_ID", max(data[employee ID])
            ),
            "Over20K",
            calculate(
                sum(data['Date'[expense]),
                filter(
                    all(data),
                    data[employee ID]= [Max_ID]  )
                )
            )
        ,[Over20K]>20000
    )
)

Create similar on Margin and Product, When you add that to the matrix/table , it will give such items

here over 20K should be your grouped margin

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[geotech]

@amitchandak , thank you for the example. When you say "create similar on Margin and Product", do you mean to create a new measure for each, similar to the one below? Or, would I include a series of columns for Margin and Product within the ADDCOLUMNS function in the example measure?