locked
How to do product with sum using DAX RRS feed

  • Question

  • Hi Everyone,

    How I can get the Value result using DAX:

    Calculation for Value on 3/1/2014: 6*20 + 10*10 + 4*30 = 340 

                                       4/1/2014: 8*30 + 2*10 + 0*40 = 260 ..... so on...

    Please reply on this...

    Performance of the measure also the big concern

    Thursday, April 2, 2015 11:19 AM

Answers

  • Hi, sorry, just recognized that this was still open:

    This would be your measure:

    SUMX(Table1; SUM(Table1[Cur1])*SUM(Table2[Cur1])+SUM(Table1[Cur2])*SUM(Table2[Cur2])+...)

    But having a look again: What do you mean with that these tables are generated from different measures? Are these result-tables/Pivot-perspective on your PP data model?

    Solution would probably look like this: SUMX(TableInYourDataModel; [Measure1]*[Measure2])


    Imke

    • Proposed as answer by Michael Amadi Monday, April 13, 2015 8:11 AM
    • Marked as answer by Charlie Liao Thursday, April 23, 2015 9:41 AM
    Sunday, April 12, 2015 2:14 PM
    Answerer

All replies

  • Especially if you are worried of performance issues, you should use Power Query as a tool for preparing your data before loading into your Power Pivot data model.

    In this case: Unpivot both tables, join them on Date & Currency code and create a new column: CurrVal(from first table) * CurrVal (from second table). Then group on Date, Sum your new value and ignoare Curr-Columns: There goes your target value.

    Then directly load to PP data model if you’re using 2013.


    Imke

    Thursday, April 2, 2015 12:32 PM
    Answerer
  • Thanks for reply...


    The first table and second table both are the sample data which generated from 2 different measures... so I can not create any calculated column... I want to do the calculation using DAX measure only...with use of those tables..

    Thursday, April 2, 2015 4:52 PM
  • Hi, sorry, just recognized that this was still open:

    This would be your measure:

    SUMX(Table1; SUM(Table1[Cur1])*SUM(Table2[Cur1])+SUM(Table1[Cur2])*SUM(Table2[Cur2])+...)

    But having a look again: What do you mean with that these tables are generated from different measures? Are these result-tables/Pivot-perspective on your PP data model?

    Solution would probably look like this: SUMX(TableInYourDataModel; [Measure1]*[Measure2])


    Imke

    • Proposed as answer by Michael Amadi Monday, April 13, 2015 8:11 AM
    • Marked as answer by Charlie Liao Thursday, April 23, 2015 9:41 AM
    Sunday, April 12, 2015 2:14 PM
    Answerer