Filter and sum

[bvsaicharitha]

Hi

 Table 

Company Sales 

x              200

y              100

x               50

z              150

z               80

I want to calculate the total sales of a company / sum of all sales.

For example for x- (200+50)/(total sum of sales)  

I am using SUMX( filter( table, table[Company]),table[Sales], [Sales])/ Calculate ( SUM(Tables[Sales], ALL(Table[Sales])

I am not able to filter the column: Its showing me an error

can you please help

[v-danhe-msft]

Hi @bvsaicharitha,

Based on my test, you can refer to below steps:

1.I have entered some sample data to test for your problem like the picture below:

2.Create a measure.

Measure = CALCULATE(SUM(Table1[Sales]),FILTER('Table1','Table1'[Company]<=MAX('Table1'[Company])))/CALCULATE(SUM(Table1[Sales]),ALL(Table1))

3.Create a Card visual and a Table visual, add the related fields. Now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/bq5ir6n8r61ii5s/Filter%20and%20sum.pbix?dl=0

Regards,

Daniel He

[bvsaicharitha]

Hi, 

Can you please explain me why you used max function to filter the name of the company which is text?

MAX returns the largest numeric value right?

[v-danhe-msft · Accepted solution]

Hi @bvsaicharitha,

could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft]

Hi @bvsaicharitha,

The max function is an aggregate function, it could be used in the formula to filter the content of a column, not only the numeric value, you could also filter the text, date, time and other contents.

Regards,

Daniel He