Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX Measure

I am trying to create a measure where when I place it in a Pivot Table the values are displayed properly and not the sum of the values.

I have two tables, see below, and I want to multiply the Qty column by the appropriate pricing in column Price. The tables are called Table1 and Table2 respectively.

I created a measure called:

Prices := Calculate(SUM('Table2'[Price]))

and a measure called

Gross Profit := SUMX('Table1', 'Table1'[Qty] * [Prices]

When the data is displayed in a pivot table, the Prices are summed and I want the values to be the correct prices.

I have even tried the measure Prices := IF(HASONEVALUE('Table2'), Calculate(SUM('Table2'[Price]))) and that doesn't bring me back any value.

The Gross Profit measure works, however, I want the prices to be shown in my table.

DateDescriptionQty
2/1/2019Gummy Bear165
2/1/2019Mints40
2/1/2019Chocolate30
2/2/2019Gummy Bear167
2/2/2019Mints39
2/2/2019Chocolate28
2/3/2019Gummy Bear169
2/3/2019Mints42
2/3/2019Chocolate30

 

DescriptionPrice
Gummy Bear1.44
Mints7.1
Chocolate2
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Have you tried using LOOKUP from Table 1 to Table 2 to bring in the price and then multiply that by the quantity? i.e. LOOKUPVALUE(Table2[Price], Table2[Description], [Description])

View solution in original post

3 REPLIES 3
v-jiascu-msft
Employee
Employee

Hi @Anonymous ,

 

There should be a relationship between these two tables. Then you can try this formula.

 

Measure = min('table2'[Price]) * sum('table2'[Qty])

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Have you tried using LOOKUP from Table 1 to Table 2 to bring in the price and then multiply that by the quantity? i.e. LOOKUPVALUE(Table2[Price], Table2[Description], [Description])
Anonymous
Not applicable

This indeed works when you bring it into the fact table. I was looking for a measure to write where I don't have to bring the variable into the fact table.

Thanks for the help and I'm marking this as solved!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.