Request for support in creation of two measures

[Anonymous]

Hi,

I'm writing with request for support in creation of two measures in my model which is gathering market data from different sources.

1. Market share - brands quantities or values calculated to market eqivalents but providing results equal to 100% on each level, independently from periods (years, quarters, months) and from regions and assigned consultants to them
2. Penetration - above market share of single consultant calculated to average market share for selected brand.

I prepared dummy model which I can share.

I'd be thankfull for help / tips.

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Does market share require a percentage result, if yes, see the solution below.

Sample data

You can group by year and market to calculate the number of brands (without deduplication). The measure is as follows.

Brand quantities =
CALCULATE (
    COUNT ( 'Table'[Brand] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Year] = MAX ( 'Table'[Year] )
            && [Market] = MAX ( 'Table'[Market] )
    )
)

Percentage = DIVIDE([Brand quantities],CALCULATE(COUNT('Table'[Brand]),ALLSELECTED('Table')))

Regarding Penetration, it is better to give an expected result and explain it.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Example measure

Share =  divide( countrows(Table), calculate(countrows(Table), filter(allselected(Table), Table[Market] = max( Table[Market] ) )))

Avg Share =

Averagex(values(Table[Consultant]), [Share])