How can we match table city sales with card metric sales in terms of maximum sales with dax function

[Sudip_J]

Hi Teams,
@INTURI_SUPARNA_ @andre 
How can i provide the correct max sales which matches with table matric and card metric as well.

Thanks and Regards,
Sudeep Kumar

[Anonymous]

Hi @Sudip_J ,

Could you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster. 

If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you.

[Anonymous]

Hi @Sudip_J ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous · Accepted solution]

Hi @Sudip_J ,

Thank you for reaching out to Microsoft fabric community forum. Also thank you  @Ashish_Excel , @danextian , @sevenhills , @burakkaragoz  for your inputs.

Since the date which you provided is bit confusing so could you please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you.

[Anonymous]

Hi @Sudip_J ,

Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster. 

Thank you.

[Ashish_Excel]

Hi,

Try these measures

S = sum(Data[sales])

Max S = MAXX(generate(all(Data[State]),all(Data[City])),[S])

Drag the econd one to a card visual.

Hope this helps.

[Sudip_J]

@ Ashish_Excel 
I would like to thanks for helping to solve this problem but unfortunately it is not working as expected . I want each state wise city best sales(max_sales).
I am attaching the the solution results in the below-

Thanks & Regards,
Sudeep Kumar

[Ashish_Excel]

I am very confused about what you want.  Share the download link of the PBI file and show the expected result there very clearly.

[danextian]

Hi @Sudip_J 

In order to get the max sales evaluated for each distinct city and state, create a virtual table in a measure and from that pickup the max

Max City Sales Measure =
-- Calculate the maximum sales value per state and city
MAXX (
    -- Generate a summary table with state, city, and the sum of sales for each group
    SUMMARIZECOLUMNS (
        'table'[state],        -- Group by 'state'
        'table'[city],         -- Group by 'city'
        "@sales",              -- Create a new column named '@sales'
        [sum of sales measure] -- Use the [sum of sales measure] as the value for '@sales'
    ),
    [@sales] -- Return the maximum '@sales' value from the summary table
)

[burakkaragoz]

Hi @Sudip_J ,

If you're trying to highlight or filter the table to show only the city (or cities) with the max sales, matching what's shown in the card (22.64K), then you'll need a bit more than just ALLEXCEPT.

Try this approach:

1. Create a measure for the max sales:

Max Sales = MAX ( Table[Sales] )

1. Then create another measure to flag the rows that match the max:

Is Max City = 
IF (
    Table[Sales] = [Max Sales],
    1,
    0
)

1. Now, use this Is Max City measure as a visual-level filter on your table and set it to 1. That way, only the city (or cities) with the max sales will show up, matching the card.

Let me know if you're using a different visual setup or if the sales are coming from a more complex model.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[Sudip_J]

@burakkaragoz 
I would like to thanks for helping to solve this problem but unfortunately it is not working as expected.
Thanks and Regards,
Sudeep Kumar

[sevenhills]

How about this?

max by city =
CALCULATE (
    MAXX (
        DISTINCT ( 'Table'[City] ),
        CALCULATE ( SUM ( 'Table'[Sales] ) )
    ),
    ALLSELECTED ( 'Table' )
)

[Sudip_J]

@sevenhills 
I would like to thanks for helping to solve this problem but unfortunately it is not working as expected . I am attaching the the solution results in the below-

Thanks & Regards,
Sudeep Kumar