Help with selecting a single row for an expression

[fcecconi]

Here is what my measure table looks like

User_ID	Metric	Value
1	Sales	100
2	Sales	150
3	Sales	75
1	Cost	50

I am joining the measure table to another table on the user_id

I want to create a straight table and have a column for sales and cost for each user id.

Something like this

User	Location	Sales	Cost
Tom	Home Office	100	50
Steve	Field	150
Mary	Field	50

Thanks!

[robert_mika]

Could you post your data?

[Gysbert_Wassenaar · Accepted solution]

Add User and Location as dimensions to your straight table and use as expressions:

Sales: sum({<Metric={'Sales'}>}Value)

Cost: sum({<Metric={'Cost'}>}Value)

[martinpohl]

Hello!

Create a chart / straight table   >>>next

then add your dimensions User and Location          >>>next

add a formula sum({<Metric={Sales}>} Value)

and a second formula with the syntax a formula sum({<Metric={Cost}>} Value)

Then give both a label and maybe format the numbers

finish

Regards

[fcecconi]

Thanks Martin

[fcecconi]

Gysbert

I was trying what you suggested, but everything was summing up to be the same value.  What I noticed was that I had a loop in my model.  Once I removed the loop, everything worked as expected..