Unique count of brands above market share threshold

[joefelipe]

Hi - I have the following table of data (multiple week, but only one shown)

prod_id	brand_name	color	case pack	qty_sold	Week
098908	brand1	blue	8	18,395	9/8/2023
036542	brand2	blue	8	2,164	9/8/2023
036545	brand2	blue	24	25	9/8/2023
712983	brand3	blue	8	55,233	9/8/2023
464426	brand4	blue	8	36,193	9/8/2023
099208	brand1	green	8	22,121	9/8/2023
034342	brand2	green	8	2,312	9/8/2023
034345	brand2	green	24	24	9/8/2023
713858	brand3	green	1	18	9/8/2023
713883	brand3	green	8	79,483	9/8/2023
464326	brand4	green	8	46,766	9/8/2023
099704	brand1	orange	1	31	9/8/2023
099708	brand1	orange	8	52,116	9/8/2023
034642	brand2	orange	8	5,934	9/8/2023
034645	brand2	orange	24	59	9/8/2023

New to DAX and trying to create a measure to get a unique count of brands above a certain market share. I have mutiple slicers for other aspects of the product like color and case pack, and I'd like for the measure to change based on the filters. 

With no filters, the summary would look like this where MS % is just the tot_qty/SUM(tot_qty). There would be 3 unique brands above 15% market share.

Brand	tot_qty	MS %
brand1	92,663	28.9%
brand2	10,518	3.3%
brand3	134,734	42.0%
brand4	82,959	25.9%

When the data is filtered down to orange only, the summary would change as follows and there would only be one brand with market share above 15%

Brand	tot_qty	MS %
brand1	52,147	89.7%
brand2	5,993	10.3%

Any help getting started in the right direction would be appreciated.

Thanks,

Joe

[barritown · Accepted solution]

Hi @joefelipe,

Here's the measure that should help:

And in plain text:

Measure = 
VAR _total = SUM ( Data[qty_sold] ) 
VAR _tbl = ADDCOLUMNS ( SUMMARIZE ( Data, [brand_name], "tot_qty", SUM ( Data[qty_sold] ) ), "MS %", DIVIDE ( [tot_qty], _total ) )
RETURN COUNTX ( FILTER ( _tbl, [MS %] > 0.15 ), [brand_name] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[joefelipe]

@barritown -

This is perfect - appreciate the response.

Thanks,

Joe