Retrieve the value from text field based on condition and put it in each row in the table

[Alyona_BI]

Hi all! 
I have the task and I can't find the solution by myself and didn't find any solution at forum, so I am really hope for your help. 

I have the following table: 

Plant	Product	Value
AAA	Water	10
AAA	Juice	15
AAA	Chips	25
BBB	Water	50
BBB	Juice	10
BBB	Chips	10
CCC	Water	5
CCC	Juice	55

I need to identify the plant which have the biggest sum of Value and to put the name of the plant in each row. So the result should be (plant BBB has the maximum sum of value = 70, while AAA has 50 and BBB has 60): 

Plant	Product	Value	Plant with maximum sum of value
AAA	Water	10	BBB
AAA	Juice	15	BBB
AAA	Chips	25	BBB
BBB	Water	50	BBB
BBB	Juice	10	BBB
BBB	Chips	10	BBB
CCC	Water	5	BBB
CCC	Juice	55	BBB

Additional comments: in reality the table is much bigger with a lot of extra columns. The plant with biggest value should be identified dynamically based on selections in slicers. 

Could anybody help me to calculate this? 

Thanks in advance!

[sturlaws]

could you share a screenshot of your data model, specifically the relationship between the product dimension and the fact table?

[Alyona_BI]

@sturlaws both of them are in the same table. I created the example in PBI, added both formulas (mine and yours) and put it in the google drive: 

https://drive.google.com/file/d/1amtgjt9v7v7Fu5DcGxIgsCvFQGp0OkJf/view?usp=sharing

In this example at the tab "Task" I added description of my complete real task, so maybe the context will bring more understanding.. 

[Alyona_BI]

@sturlaws hi! Thanks for your help. 

I have my own a little bit similar calculation: 

VAR _Table = CALCULATETABLE(

GROUPBY(Table,

Table[Plant],

"ND", SUMX(CURRENTGROUP (), Table[Value]))

)

VAR _Max = MAXX(_Table, [ND])

RETURN MAXX(FILTER(_Table, [ND] = _Max), [Plant])

It doesn't work. Unfortunately your variant too. The result is: 

So the result in each row should be "Packer-Deparker", which we see in the Total row, but not in all rows (( 

Maybe you have an idea why this is so?

[sturlaws · Accepted solution]

It is because you have not created the separate product-table, I have updated the pbix-file you shared here pbix

[Alyona_BI]

It works. Many thanks! 

[sturlaws]

Hi, @Alyona_BI,

try to create this measure:

MaxPlant =
VAR _product =
    VALUES ( DimProduct[Product] )
VAR _allPlants =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'Table'[Plant] ),
            "sumValue", CALCULATE ( SUM ( 'Table'[Value] ) )
        ),
        FILTER ( ALL ( 'Table' ), 'Table'[Product] IN _product )
    )
VAR _maxValue =
    MAXX ( _allPlants, [sumValue] )
VAR _maxPlant =
    MAXX ( FILTER ( _allPlants, [sumValue] = _maxValue ), 'Table'[Plant] )
RETURN
    _maxPlant

For this measure to work, you have to create a product-table, dimProduct, with all the distinct values for products. dimProduct should not have a relationship to the main-table, or have the relationship set to inactive.

Add DimProduct[Product] to a slicer in your report, and add the MaxPlant-measure to the table-visual:

If you want the slicer to filter the table, you can create this measure

filterProduct = var _tableProduct = VALUES('Table'[Product])
return
COUNTROWS(FILTER(VALUES(DimProduct[Product]),_tableProduct in VALUES(DimProduct[Product])))

and add it to the filter pane of the table-visual: 

I am behind a very strict firewall today, so I have no way of sharing the sample report with you, sorry about that.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.