use columns from another table DAX

[siva_powerbi]

I have two tables one table has the real time data with dimensions and measures, and other tables is manual table with values of one of the dimension.

Example data (Apologize for few records):

Dimension     amount

Hard Disk        400

Mouse             100

Keyboard         200

My report requirement is to add the rows at end of these items as "Total Sales", "Net Sales" for each store

To achieve this I have create a manual table with data

Store   Dimension

1           Hard Disk

1           Mouse

1           KeyBoard

1           Total Sales

1            Net Sales

Now I have use manual table in report and want to extract the values from real time table I should be able to add formulas for "Total Sales" and "Net Sales" and display data for remaining attributes as it is, linked both tables on store id. Tried many ways but without success

Out of ideas on how to start with this requirement, I can display data if everything comes from single table but in this scenario I am unable to start at first point.

Any help is greatly appretiated.

Thanks

Siva

[Anonymous · Accepted solution]

Hi  @siva_powerbi  ,

According to your description, I create this data：

Table1:

Table2:

Table3:

Here are the steps you can follow：

1. Create calculated column.

Column =
var _grouptotals=CALCULATE(SUM('Table1'[amount]),FILTER('Table1','Table1'[Date]<=TODAY()&&'Table1'[Store]=EARLIER('Table2'[Store])&&'Table1'[Dimension]=EARLIER('Table2'[Dimension])))
var _totals=CALCULATE(SUM('Table1'[amount]),FILTER('Table1','Table1'[Date]<=TODAY()&&'Table1'[Store]=EARLIER('Table2'[Store])))
var _counthd=CALCULATE(COUNT('Table1'[Dimension]),FILTER(ALL('Table1'),'Table1'[Dimension]=MIN('Table2'[Dimension])))
var _allpurchase=SUMX('Table3','Table3'[Purchase price]*_counthd)
return
SWITCH(
    TRUE(),
    'Table2'[Dimension]="Total Sales",_totals,
    'Table2'[Dimension]="Net Sales",_totals-_allpurchase,
    _grouptotals)

2. Result

Table1 has not been updated yet, the data of Table2:

After Table1 is updated:

Data in Table2 table after update:

You can downloaded PBIX file from here.

If my answer is not what you want,can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

Best Regards,

Liu Yang

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

[amitchandak]

@siva_powerbi , If you want to add to tables, that would static will not change with filter. If you append only UI difficult to get that.

Which one you want?