topic Re: Company with Maximum profit per state in DAX Commands and Tips

Company with Maximum profit per state

deepak_d03 — Wed, 03 Jan 2024 03:19:46 GMT

Here is the sample data. For each state, I need to show the company having the maximum profit. (profit = premium written - losses paid - taxes paid).

This is what I have so far and the output, but not sure how to proceed to include the Insurance company corresponds to the maximum profit.

EVALUATE

SUMMARIZECOLUMNS(

'IOWA'[State],

"Max Profit", MAXX(

'IOWA',

'IOWA'[Profit]

)

Re: Company with Maximum profit per state

Dangar332 — Wed, 03 Jan 2024 06:08:12 GMT

hi, deepak_d03

as i understand your problem
below is my data and code for output as you want

data image

code
just adjust column and table name

EVALUATE ADDCOLUMNS( ALL('Table'[state]), "company name", CALCULATE( MIN('Table'[company name]), 'Table'[profit]=MAXX( FILTER( 'Table', 'Table'[state]=EARLIER('Table'[state]) ), 'Table'[profit] ) ), "profit", CALCULATE(max('Table'[profit])) )

output image

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

and don't forgot to give kudos

Re: Company with Maximum profit per state

Dangar332 — Wed, 03 Jan 2024 06:42:35 GMT

Hi, deepak_d03

for create new table in power bi use below formula

Table 2 = ADDCOLUMNS( ALL('lowa'[state]), "company name", var a = MAXX( FILTER('lowa', 'lowa'[state] = EARLIER('lowa'[state])),'lowa'[profit]) RETURN MAXX( FILTER( 'lowa','lowa'[profit]=a),'lowa'[company name]), "profit",MAXX( FILTER('lowa', 'lowa'[state] = EARLIER('lowa'[state])),'lowa'[profit]) )

Re: Company with Maximum profit per state

tamerj1 — Wed, 03 Jan 2024 08:20:49 GMT

Hi deepak_d03
Please try

EVALUATE SUMMARIZECOLUMNS ( 'IOWA'[State], "Max Profit", MAXX ( 'IOWA', 'IOWA'[Profit] ), "Max Profit Company", MAXX ( TOPN ( 1, 'IOWA', 'IOWA'[Profit] ), 'IOWA'[Company Name] ) )

Re: Company with Maximum profit per state

Dangar332 — Wed, 03 Jan 2024 08:25:17 GMT

Hi, tamerj1

i am learn new thing from you that another use of TOPN() function

Re: Company with Maximum profit per state

Anonymous — Wed, 03 Jan 2024 09:31:39 GMT

Hi deepak_d03 ,

Please follow these steps:

Summarize the data by State, Company Name columns, filter the data with Ranking=1, assign to __ValueFilterDM0 variable.
Summarize data by State, Company Name columns for __ValueFilterDM0 and assign to __DS0Core variable.
Select the first 501 rows of data and assign them to the variable __DS0PrimaryWindowed, with "1" representing ascending order and "0" representing descending order.
Display the data for __DS0PrimaryWindowed.

The original data is as follows:

The DAX code is as follows:

DEFINE VAR __ValueFilterDM0 = FILTER( KEEPFILTERS( SUMMARIZECOLUMNS( 'IOWA'[State], 'IOWA'[Company Name], "Profit", 'IOWA'[Profit], "Ranking", IGNORE('IOWA'[Ranking]) ) ), [Ranking] = 1 ) VAR __DS0Core = SUMMARIZECOLUMNS('IOWA'[State], 'IOWA'[Company Name], __ValueFilterDM0, "Profit", 'IOWA'[Profit]) VAR __DS0PrimaryWindowed = TOPN(501, __DS0Core, 'IOWA'[State], 1, [Profit], 0, 'IOWA'[Company Name], 1) EVALUATE __DS0PrimaryWindowed ORDER BY 'IOWA'[State], [Profit] DESC, 'IOWA'[Company Name]

Run the result as follows:

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Company with Maximum profit per state

deepak_d03 — Wed, 03 Jan 2024 22:17:00 GMT

Thank you! It worked.

If I need to show the info as a report, How do I do that?

Re: Company with Maximum profit per state

tamerj1 — Thu, 04 Jan 2024 04:20:44 GMT

deepak_d03

Create [Max profit] and [Max Profit Companies] as measures and place them along with IOWA[State] column in a table visual.

Re: Company with Maximum profit per state

deepak_d03 — Tue, 09 Jan 2024 00:07:22 GMT

Thank you! It worked.