Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
joefelipe
Frequent Visitor

Unique count of brands above market share threshold

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

prod_idbrand_namecolorcase packqty_soldWeek
098908brand1blue818,3959/8/2023
036542brand2blue82,1649/8/2023
036545brand2blue24259/8/2023
712983brand3blue855,2339/8/2023
464426brand4blue836,1939/8/2023
099208brand1green822,1219/8/2023
034342brand2green82,3129/8/2023
034345brand2green24249/8/2023
713858brand3green1189/8/2023
713883brand3green879,4839/8/2023
464326brand4green846,7669/8/2023
099704brand1orange1319/8/2023
099708brand1orange852,1169/8/2023
034642brand2orange85,9349/8/2023
034645brand2orange24599/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.

Brandtot_qtyMS %
brand192,66328.9%
brand210,5183.3%
brand3134,73442.0%
brand482,95925.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%

Brandtot_qtyMS %
brand152,14789.7%
brand25,99310.3%

 

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

Thanks,

Joe

1 ACCEPTED SOLUTION
barritown
Super User
Super User

Hi @joefelipe,

Here's the measure that should help:

barritown_0-1695717584698.png

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

 

View solution in original post

2 REPLIES 2
barritown
Super User
Super User

Hi @joefelipe,

Here's the measure that should help:

barritown_0-1695717584698.png

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

 

@barritown -

This is perfect - appreciate the response.

Thanks,

Joe

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.