Calculated column based on the mix of unique values?

[Mk60]

Hope I can find if this is possible to acomplish with calculated column? What I am ytying to solve for is to create new column, (assuming that's solution and not Measures), to be able to list Client names where TYPE will be assigned based on the type with largest Balance at the time, AND combined all balances for that client under same name, kepping only TYPE which was associated with largest balance. 

In this example, from TABLE 1 I want to create column that will have CLIENT-1 showing only TYPE A, (which had largest balance), AND for the same CLIENT-1 show Aggregated Balance (as shown for each collor). As shown in TABLE 2.

Please let me know if there is way to accomplish this. Much appreciated!

Corrected TABLE 2, sorry about my error--CLIENT-3 should be TYPE A, which was one with largest balance:

[AlexisOlson · Accepted solution]

If I load your data into a table like this:

Then I can create a matrix visual like this using the measure I defined above:

This is what I understood your request to be. What am I missing?

[AlexisOlson]

There are various ways to do this. I wrote about a couple of them here:

https://stackoverflow.com/questions/52525377/return-top-value-ordered-by-another-column

This TOPN option is fairly straightforward:

Table2[TYPE] =
VAR T1 = FILTER ( Table1, Table1[CLIENT] = Table2[PRIMARY CLIENT] )
RETURN
    MAXX ( TOPN ( 1, T1, Table1[BALANCE] ), Table1[TYPE] )

[Mk60]

Alexix, first thank you so much for your reply. Second, I wanted to clarify that TABLE 1 and 2 here are just what I named my two excel tables trying to explain my sample. My data table is called "Query1" and field names in that table are CLIENT, TYPE and BALANCE. I was trying to see how can I group all clients as I had in my excel "TABLE 2", just as an example. Is the calculated column righ solution at all. So much tenkful for your help.

[AlexisOlson]

Unless you have a reason to create a second physical table, then it's probably better just to write a measure that you can use in a pivot table (instead of having Table2).

Writing it as a measure changes things slightly since the filtering is done automatically within that single table

Top Type = MAXX ( TOPN ( 1, Query1, Query1[BALANCE] ), Query1[TYPE] )

[Mk60]

Alexis, this "Top Type" measur does work, but not giving me what I want here. I probably can't explain the best what I need to get to. Hope this context helps more: I am using single table "Query1" in my PBi data model. I have thousands of CLIENTS and probably few thousands of TYPES and BALANCES for each client record. My best guess is I would need new column with only one line/record for each slient, based on the criteria I tried to explain in my tables above. In that single record for each client, it would be showing only TYPE that was associated with largest balance among all multiple records for each unique client, AND show aggregated balance in that same record for each client. Hope this might be of more help. Greatfull for your time and understanding. Needles to say, I am realively new to PBI.

[Mk60]

If I may, I'll try to simplify little more, please forgive me bothering you with this:

So what I get from my Query1 data table is this list of clients with these columns: Date, Client, Type and Balance, (sample list of 3 clients with multiple Types and Balances in the pic below)

What calc can be done so I can get list of individual clients, only listed once, but with only one value for "Type" that had largest balance for each Client, AND show single aggregated balance like in the list below: 

Again, sorry for bothering and trully apprecite any thoughts on this!

Best!

[AlexisOlson · Accepted solution]

If I load your data into a table like this:

Then I can create a matrix visual like this using the measure I defined above:

This is what I understood your request to be. What am I missing?

[Mk60]

Hi Alexis, I have separate question, and since it is directly related to the sample that you gave me solution for, I felt I should try to ask you again directly, if you could kindly be able to provide solution for this question. What I need is basically to use the Total Balance in your sample above, (which is a subtotal of those clients only), and use that balance as numerator in another calculation in order to get percentage of total Balance, (which is larger balance that I do have as attribute in my data already). So I feel I need to define some new measure somehow for this Total balance of 20,253,600.75? I hope you may have some dax solution so I can acomplish that? Thanks you so much in advance, once again.

[Mk60]

Alexis, as I was trying to work on your calculation with my actual data, I just realized that this should work for what I am trying to acomplish, as I am matching clients and the types as expected. I see you are using Matrix visual in your PBI sample, and I was using Table visual instead in my case, but I assume that should not matter. What I am trying to clean now, (in both visual versions), is I want to only keep total for Balance column, but not for "Top Type" or any other column I may include, like where you have C in your Total row. I was just navigating through visual formatting and it looks like I can either remove Totals for Rows completely or have them for all columns, but not for only selected column? Any tips on that, but I am so happy to tell you that your solution might be just what I need. I'll just need to adjust and rename some attributes for my table visual. I trully apprecite you staying on me with this, and your dedication and willingness to assist new PBI users. Such a pleasure to run into super usres like yourself. Hope to connect again.  

[AlexisOlson · Accepted solution]

You can suppress the total line using ISINSCOPE in the measure or set the subtotaling per column level as mentioned in this thread:

https://community.fabric.microsoft.com/t5/Desktop/Remove-totals-and-sub-totals-for-some-columns-in-a...

Top Type =
IF (
    ISINSCOPE ( Query1[CLIENT] ),
    MAXX ( TOPN ( 1, Query1, Query1[BALANCE] ), Query1[TYPE] )
)

[Mk60]

Thanks a lot again, Alexis! Your dax formula fix worked for my measure. I also did check the thread you mentioned here, just in case I need a specific column totals at some point, but to be honest these screenshots are from some old versions that I do not even see it in my May2024 version of Desktop. I used white font to make total invisible, but will check if I can find similar options in new versions. Regardless, much appreciate your help!

All the best!

Marin

[Mk60]

Small error on my part, CLIENT-3 should have TYPE A, bc that was one with largets balance. Sorry about that: