If multiplication from 2 different tables

Hi all

How can I get a multiplication from 2 different tables that have in common a column with an IF scenario.

The multiplication consists of 2 values (sales and ingredient 1 value) from 2 different tables.

The formula (and expected result) I did in excel is the followning:

The multiplication of these 2 values will depend on date fields. Pretty much if the column I (date) value is within the columns E and F, then apply the multiplication if not, leavre a zero.

I attach the expected result and pbix.

https://1drv.ms/u/s!ApgeWwGTKtFdhkHJA5ZPhxb-6AoH?e=FJbbY8

Thanks community.

Show and Tell

v-alq-msft

6 years ago

Hi, o59393

I have done some optimizations for the dax. It works in the sample data. But I am not sure if it works in your final pbix file. You may try the following calculated column.

ResultColumn = 
var _date = 'Sheet1 (2)'[Date]
var m = 'Sheet1 (2)'[Merge]
return
CALCULATE(
    SUM('Formulas'[Value]),
    'Formulas',
    'Formulas'[Merge] = m ,
    _date>=Formulas[Validity Start Date],
    _date<=Formulas[Validity End Date],
    Formulas[Attribute]="Ingredient 1"
)*'Sheet1 (2)'[Sales]*0.01
/CALCULATE(
        COUNTROWS('Formulas'),
        'Formulas',
        'Formulas'[Merge] = m ,
        _date>=Formulas[Validity Start Date],
        _date<=Formulas[Validity End Date],
        Formulas[Attribute]="Ingredient 1"
)

Resutl:

Best Regards

Allan

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

14 Replies

v-alq-msft
Community Support
6 years ago
Hi, o59393

Based on your description, you may create a measure as follows.

Result = var _date = SELECTEDVALUE('Sheet1 (2)'[Date]) var _start = SELECTEDVALUE(Formulas[Validity Start Date]) var _end = SELECTEDVALUE(Formulas[Validity End Date]) var _sales = SELECTEDVALUE('Sheet1 (2)'[Sales]) return IF( ISFILTERED(Formulas[Merge]), IF( _date>=_start&&_date<=_end, _sales*[Ingredient 1], 0 ) )

Result:

Best Regards
Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- o59393
  Post Prodigy
  6 years ago
  Hi v-alq-msft
  
  The dax is perfect 😄
  
  I just have one problem, the result shows empty when I just select the column merge and the result measure:
  
  I guess it appears empty since in order to work, I have to select all the columns that point to the selectedvalue.
  
  So, can the formula be added to the table Sheet1 (2) as a new column? :
  
  In that way the result will not depend of the selectedvalue, and therefore drag and drop it to a table.
  
  Thanks Allan.
  - v-alq-msft
    Community Support
    6 years ago
    Hi, o59393
    
    If you want to create a column, you may try the following formula.
    ResultColumn = var _date = 'Sheet1 (2)'[Date] return SUMX( DISTINCT( FILTER( RELATEDTABLE(Formulas), _date>=Formulas[Validity Start Date]&& _date<=Formulas[Validity End Date] && Formulas[Attribute] IN {"Ingredient 1"} ) ), 'Sheet1 (2)'[Sales]* CALCULATE(max(Formulas[Value])) *0.01 )
    
    Result:
    
    Best Regards
    Allan
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
6 years ago
Multiplication Required common context so you have to use a formula like

sumx(summarize(Dim,dim[Item1],"_1",[Measure table1],"_2",[Measure table1]),[_1]*[_2])

Refer for row context

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451
- o59393
  Post Prodigy
  6 years ago
  hi amitchandak
  
  I think the dax you provided is missing the dates interval. How should the formula be considering these dates fields from 2 different tables?
  
  Thanks!
  - amitchandak
    Super User
    6 years ago
    The case when both should have a common date dimension.
    
    https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451
    
    When you want to have two different ranges
    
    https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
    
    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/