Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Finding value based on row header and a column value

Good Day   I want to extract information from another table based on 2 variables. In the below data set I want to determine the "Tub Factor" column so I can multiply it by the loads. Call the below...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    HI Anonymous 
    Here is the sample file with the solution based on unpivotted table https://we.tl/t-mPd68FBRV3

     

    Tub Factor = 
MAXX ( 
    FILTER (
        RELATEDTABLE ( 'Material List' ),
        'Material List'[Fleet Size] = 'Daily Production'[Fleet Size]
    ),
    'Material List'[Value]
)

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Anonymous 
If you you unpivotted the table then you are on the right track. Why aren't you able to create a relationship? Still you can match even without a relationship bu the relationship would be much easier and faster. Still not clear what exactly are you trying to match and which value are you trying to grap from which table to which table. Cannot fund the Material Type column anywhere in the provided screenshots. Also what exactly is the Tub Factor? Is it the value that you are trying to calculate?
lease provide a simplified dummy sample data along with the expected results.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Sorry, I mean't Material List, not Material Type. I am unsure how to attach a sameple set, should I attach as an excel or a Power BI?

 

Just to simplify and elaborate.

 

I have my main Data "Daily Production" and a table that I need to draw info from "Material List".

 

I was the tub factor column to first check the Fleet size and match the column, and then return associated value in the row matching the Application. Below in the tub factor column is what should be returned.

Daily Production
EIIDApplicationFleet SizeTub Factor
AD4001MMOAD4013.95
AD4001GHAAD4014.55
AD6001ZRTAD6022
AD6002MMOAD6018.5

 

Material List
ApplicationAD20AD25AD30AD40AD50AD60RD90
MMO  10,513,95 18,531
GHA  10,514.55 1931
ZRT   15 2227
BPO   18  30

 

Below is the error I get if I unpivot

 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 
    Better to share PBI sample file. You can share a download link via onedrive, dropbox, weshare, etc..