Forum Discussion

siva_powerbi's avatar
siva_powerbi
Helper IV
5 years ago
Solved

use columns from another table DAX

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

 

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.