Forum Discussion

outmat's avatar
outmat
Regular Visitor
4 years ago
Solved

DAX Measure - Multiplying Columns from Separate Tables

I'm wondering if anybody has found a way to get a DAX measure to multiply two columns by each other at the row level, and then make sure the totals manage to also add up. I'm currently running some cost tracking in PBI and wanted to multiply quantities by a separate table with the rates assigned to each item type. 

Has anyone found a way to specifically do this within a DAX measure and avoid calculated columns or power query? I've got the below code which works when filtering by each item type (which is linked to the rates table), but when introducing multiple item types, it summarizes all the active item type rates and multiplies each entry by that. Alternatively, using SELECTEDVALUE in place of SUM with the rates lookup also works, but then the totals return a blank value. 

CALCULATE(SUM(items[quantity])* SUM(rates[unit_rate]),
FILTER(items,items[type] <> BLANK())
)



    CALCULATE(SUM(items[quantity])* SUM(rates[unit_rate]),
    FILTER(items,items[type] <> BLANK())
        )

 

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi outmat 

    Yes this possible but depends on you data model. Would you please share a screenshot of your data model?

    • v-xiaotang's avatar
      v-xiaotang
      Community Support
      4 years ago

      Hi outmat 

      Thanks for your sharing.

       

      Best Regards,

      Community Support Team _Tang

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

  • outmat's avatar
    outmat
    Regular Visitor
    4 years ago

    I actually renamed the columns and tables for simplicity's sake, but it would be a many(items)-to-one(rates) with one-way filtering (rates filter items).