Forum Discussion

bvsaicharitha's avatar
bvsaicharitha
Helper I
8 years ago
Solved

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 sa...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    8 years ago

    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's avatar
v-danhe-msft
Microsoft Employee
8 years ago

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's avatar
bvsaicharitha
Helper I
8 years ago

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's avatar
    v-danhe-msft
    Microsoft Employee
    8 years ago

    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

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    8 years ago

    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