Retain original share despite filtering

[sukram1]

Hi,

I have a dataset which contains sales figures of various companies over several years. For each year there is a row "All", which represents the sum of all companies; shown here as an example (called Table_share):

I can calculate the market share per year without any problems (I have filtered out the sum of all companies "All"):

Market Share = DIVIDE(SUM(Table_share[Sales]);CALCULATE(SUM(Table_share[Sales]);ALLSELECTED(Table_share));0)

Now the data set is to be divided into peers. In addition to the share of the companies in the peer, the original market share of the companies per year is also to be shown:

The result should look like this:

How can I keep the sum of the total sales per year to be able to calculate the share independent of the filtering of the peers?
to be able to calculate? I have created a measure with which I have used "All". But then I get the proportion over all years. I can use "Filter" to restrict to one year, but then I can no longer filter the years.

Thanks in advance!

Markus

[sukram1 · Accepted solution]

Hello,
I have found the solution myself. Maybe this solution will help someone 😊
I first filtered the data and removed the "All" entry for Company. I then created the following measure:

Total_Sales = 
CALCULATE(
    SUM(Table_share_wo_all[Sales]);
    REMOVEFILTERS(Table_share_wo_all);
    VALUES(Table_share_wo_all[Year])
)

This now shows the total value of the sales, since I always want to show the original market share when filtering by peers

As you can now see, the share within the selected peer is now calculated under Market Share. The last column still shows the original market share.

[sukram1]

Hi,

@MAwwad Thank you for the detailed explanation. That has again made some things clearer. For the second Measure Peer Market Share, I get an error message. However, I have not yet found out what exactly I did wrong. My guess was that I set the brackets wrong somewhere.

@FreemanZ 
Thank you for your help. I have implemented your suggestion and unfortunately I get no result when I select a peer.

The peers are not original data but simply a grouping of companies. In the original dataset it is an added column, which makes the assignment with nested If statement. Here, companies and business lines are taken into account. I'm not entirely happy with the peers, because it is currently a 98 if-and statement. The point here is that other companies and business lines are of interest to the users in each case. Via a slicer, the respective users can then simply make their desired choice. It is easier to set only 1 filter than to set 3 different slicer.

If you need more explanation about the peers, just let me know.

Thanks a lot for your help!

Markus

[MAwwad]

To calculate the market share of each company within a peer group while retaining the original market share of each company across all years, you can create two measures:

1. Original Market Share: This measure will calculate the market share of each company across all years, regardless of any filters applied. You can use the following DAX expression:

Original Market Share = DIVIDE( SUM(Table_share[Sales]), CALCULATE( SUM(Table_share[Sales]), ALL(Table_share[Company], Table_share[Year]) ), 0 )

This measure uses the ALL function to remove any filters applied to the "Company" and "Year" columns, and calculate the total sales across all companies and years. It then uses the DIVIDE function to calculate the market share of each company based on the total sales.

1. Peer Market Share: This measure will calculate the market share of each company within a peer group. You can use the following DAX expression:

Peer Market Share = DIVIDE( SUM(Table_share[Sales]), CALCULATE( SUM(Table_share[Sales]), ALLSELECTED(Table_share), NOT(ISFILTERED(Table_share[Company])) ), 0 )

This measure uses the ALLSELECTED function to remove any filters applied to the "Year" column, but keep the filters applied to the "Company" column. It also uses the NOT and ISFILTERED functions to ensure that the "Company" column is the only one being filtered, and calculates the market share of each company based on the filtered sales.

To show both market shares in the matrix visual, you can add both measures to the "Values" section and drag the "Company" and "Year" columns to the "Rows" section. The original market share will be the same for each year, while the peer market share will change based on the selected peer group.

I hope this helps! Let me know if you have any further questions.

[FreemanZ]

hi @sukram1 

try like:

Market Share Total = 
DIVIDE(
    SUM(Table_share[Sales]),
    CALCULATE(
        SUM(Table_share[Sales]),
        Table_share[Company]="all"
    ),
    0
)

in case of issue, please tell us how peers column is related to other columns?