Filtering

[Anonymous]

HI!

Could you please help me with filtering:

I have theese measures: Country; Producer; Category; SubCategory; Month.

I have calculated Market Share of Producers, where I take for base 1Month and all brands.

SHARE%  = CALCULATE(SUM(db[Volume]);FILTER(db;db[Month]=db[Month]))/CALCULATE(SUM(db[Volume]);ALL(db[Month];db[Producer]))

To show TOP 5 Producers I put in TOP N measure share%.

So on Chart I see top 5 Producers Market Share by Months.

If I want to see top5 Producers' shares in certain Category, I choose Category I need and than I see top5 Producers' market share (for 100% base it takes data slice 1month&all producers&Category).

Problem appears when I choose only some categories. In this case BI takes for total base (I mean base on which I devide separate Producers sales, this part of measure - CALCULATE(SUM(db[Volume]);ALL(db[Month];db[Producer])) ) calculation volume of segments where are sales of top5 producers - not full sales of all theese categories (there are not all categories i have chosen in filter).

Maybe there is a problem with the formula? Or how can I calculate ALL sales in chosen Categories for TOP5 Producers, even if they are not in theese categories (no sales)?

Thank you!

[Anonymous]

The main problem is when using filter TOP N, the base is (denominator) is changing too, but in reality it should show all sales (depending on what data type is choosing user, in my case - category). 

Maybe there is another way of top filtering. For example, Is it possible to put top 5 only in numerator? But there will be one condition - it shouls show the same top5 producers during the all time periods, (top5 filtered by last period of time)

[v-shex-msft]

Hi @Anonymous ,

You can try to use following measure formula, it will summary all filtered(topn and other filters) records and grouped by current month:

All Sales Grouped by month =
CALCULATE ( SUM ( db[Volume] ); ALLSELECTED ( db ); VALUES ( db[Month] ) )

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft ,

Tahnk you for helping me!

Unfortunately thre result is wrong. Because using formula "allselected" it calculates base only for top5 producers. For example, sales sum of top5 producers are 219kg, but full sales are 279kg - so fo calcalating for market share of top5 it will take 279kg, not 219). in the picture you can see the result of your formula - first column means sales by Producer, second column meanstotal sales of top5, but there shoul be 279kg.

I hope this try will not stop you in helping me)))))))))))))))))

[v-shex-msft]

Hi @Anonymous ,

You can try to use below formula to get percent of all sales based on current month group:

Percent of all sales grouped by month =
DIVIDE (
    CALCULATE (
        SUM ( db[Volume] );
        ALLSELECTED ( db );
        VALUES ( db[Month] );
        VALUES ( db[Producer] )
    );
    CALCULATE ( SUM ( db[Volume] ); ALL ( db ); VALUES ( db[Month] ) );
    BLANK ()
)

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft ,

Thank you, but unfortunately it does not solve my question. 

Please, read my comment below, maybe you have a solution))))