How to represent 2 values after a max of a third

[danidelriego]

Hi all,

First of all, I must admit that I am a total noob in Power BI, so sorry if this is simple, but I just can't figure it out.

I am working on project in which I need to represent the value of 2 columns when the value of a third column (the multiplication of the other 2) is maximum.

Column A	Column B	Column C (multiplication)
5	2	10
4	3	12
1	4	4

So, in this case, I need to represent the second row (A=4 & B=3), as Column C reaches its highest value. However, when I try to plot A and B in a scatter graph, I can only choose maximum values for A and B, so it is totally wrong (A=5 & B=4).

I tried to figure out ways to filter by maximum value of Column C, but I can't find a solution.

Thank you in advance!

[Aburar_123 · Accepted solution]

Hi @danidelriego ,

You can achieve like this,

Create below 2 calculated columns,

Max C = CALCULATE(MAX('Table'[Column C (multiplication)]),FILTER('Table','Table'[Location]=EARLIER('Table'[Location])))

Applicable Flag = IF('Table'[Column C (multiplication)]='Table'[Max C],1,0)

then apply the filter as below

Please accept it as your solution if it answer your post. Thanks.

[123abc]

You can achieve this in Power BI by creating a calculated table that includes only the rows where Column C (the multiplication result) is at its maximum value and then using this calculated table for your scatter plot. Here's how you can do it:

1. Create a Calculated Table for Maximum Values:

   Go to the "Model" view in Power BI, then click on "New Table" in the "Modeling" tab. In the formula bar, enter the following DAX formula to create a calculated table:

MaxValuesTable =

SUMMARIZE (

     YourTable, // Replace YourTable with the name of your original table

      YourTable[Column A],

      YourTable[Column B],

      YourTable[Column C],

      "MaxValue", MAX(YourTable[Column C])

)

1. This formula creates a new table, "MaxValuesTable," that contains all the columns from your original table (Column A, Column B, and Column C) along with an additional column called "MaxValue," which stores the maximum value from Column C.

2. Create a Scatter Plot Visualization:

   Now, go back to the "Data" view or "Report" view in Power BI, and insert a scatter plot visualization. In the "Values" section of the scatter plot, drag "Column A" to the "X-axis" and "Column B" to the "Y-axis."

3. Apply Filters:

   In the "Visualizations" pane, select "MaxValuesTable" as your data source for the scatter plot.

4. Add a Tooltip:

   If you want to see the details of each point, you can add a tooltip to the scatter plot. In the "Fields" section of the "Visualizations" pane, add "Column A" and "Column B" to the tooltip field well.

Now, your scatter plot will show only the points corresponding to the rows where Column C has its maximum value, which is what you were looking for.

[danidelriego]

Thank you very much for your response.

Now I have another issue. I have another column called 'location', and I would like to group by location in a way that, for each location, I get the maximum value of C and the values of A & B that calculates C.

I have tried to use your code with modifications I found in the internet, but I can't aggregate the table.

Thank you again in advance and sorry for making the topic bigger.

Sample data:

Location	Column A	Column B	Column C (multiplication)
Spain	5	2	10
Spain	4	3	12
Spain	1	4	4
France	1	2	2
France	3	1	3
France	1	1	1

Expected result of aggregation:

Location	Column A	Column B	Column C (multiplication)
Spain	4	3	12
France	3	1	3