topic Re: Need to return value not sum in DAX Commands and Tips

Need to return value not sum

Jimbo1980 — Fri, 05 Jul 2024 19:35:56 GMT

I have 2 tables in my model:-

'Requisitions' = Many records
'ExternalFillRatio' = Unique records by division

I have starting creating a Power Pivot from the 'Requisitions' table using 'Division'. I want to bring in a column from 'ExternalFillRatio' named 'Ratio' to the corresponding divsion. I only want to bring in the value that is matched to the division. I have tried using a column i.e. = RELATED(ExternalFillRatio[Ratio]) but this isn't the answer I am looking for. This creates a grand total in the Power Pivot. All i want is the value e.g (Ratio) that matches the divsion from ExternalFillRatio table.

Does anyone know how I can achieve this? Any help is greatly appreciated.

Re: Need to return value not sum

aduguid — Sat, 06 Jul 2024 03:40:28 GMT

try this calculated column

Ratio = LOOKUPVALUE(ExternalFillRatio[Ratio], ExternalFillRatio[Division], Requisitions[Division])

Re: Need to return value not sum

Jimbo1980 — Sat, 06 Jul 2024 06:48:08 GMT

Thank you for your reply. Unfortunately it still creates the same problem.

Re: Need to return value not sum

Anonymous — Mon, 08 Jul 2024 02:54:52 GMT

Hi Jimbo1980

Maybe you can try to create a virtual table as a variable in the measure or calculated column, then it could be more easier to get the target outcome.

If possible, please provide your data(exclude sensitive data) or create some sample data, so that we can help you better.

You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.