Market share dax measure - DAX

user_19183784 0
Viewed 106

I have a simple dax measure that calculates market share % for the below table.

Row Labels Category Eaches Total cost
Supplier1 Accessories 610 £ 73,541.44
Supplier1 CRT-D 17 £ 193,078.93
Supplier1 DC ICD 23 £ 187,373.50
Supplier1 Leads 86 £ 45,835.64
Supplier1 SC ICD 7 £ 54,115.50
Supplier2 Accessories 52 £ 8,189.75
Supplier2 CRT-D 7 £ 66,065.71
Supplier2 DC ICD 3 £ 27,877.80
Supplier2 Leads 34 £ 33,277.53
Supplier2 S ICD 20 £ 223,201.00
Supplier3 Accessories 1338 £ 26,657.85
Supplier3 CRT-D 18 £ 261,798.58
Supplier3 DC ICD 15 £ 190,276.96
Supplier3 Leads 292 £ 93,502.09
Supplier3 SC ICD 6 £ 75,402.00

Dax measure:

=DIVIDE(
SUM(EACHES),CALCULATE(
SUM(EACHES),ALL(SUPPLIER)))

However, i was instructed to calculate market share for Category "DC ICD" and "SC ICD" together which really bugs me as i can't figure it out how to write this in DAX. As an example Excel formula for supplier 1 would look like this:

=(23+7)/(23+7+3+15+6).

Could someone kindly advise how to move this into DAX please?

dax excel
1 Answers

Something like this may work

=DIVIDE(
       CALCULATE(
                SUM(EACHES)
                ,OR(
                   'table'[CATEGORY]="DC ICD
                   ,'table'[CATEGORY]="SC ICD"   
                )
    )        
    ,CALCULATE(
              SUM(EACHES)
              ,ALL(SUPPLIER)
              ,OR(
                  'table'[CATEGORY]="DC ICD"
                  ,'table'[CATEGORY]="SC ICD"   
               )
      )
)

If you want to see results only in a certain row then

=
VAR Result =
      DIVIDE(
         CALCULATE(
                       SUM(EACHES)
                       ,'Table'[CATEGORY] in {'DC ICD','SC ICD'}   
                        )
             )        
              ,CALCULATE(
                        SUM(EACHES)
                        ,ALL(SUPPLIER)
                        ,'Table'[CATEGORY] in {'DC ICD','SC ICD'}   
              )
        )

RETURN
      IF(
         SELECTEDVALUE('Table'[CATEGORY]) in {'DC ICD','SC ICD'}   
         ,Result
         ,BLANK()
       )
user_190423790
Related