Forum Discussion
Filter and sum
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
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
4 Replies
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
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?
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
