topic DAX for sum of one column based on column in another table in DAX Commands and Tips
https://community.powerbi.com/t5/DAXCommandsandTips/DAXforsumofonecolumnbasedoncolumninanothertable/mp/728336#M1614
<P>Hi all,</P><P> </P><P>Table1 and table2 has manymany relationship so I solved that relationship by creating bridge table.</P><P>So I have 3 tables: table1, table2, bridge table</P><P> </P><P>Table1bridge has Many to 1 relationship</P><P>Table2bridge has Many to 1 relationship</P><P> </P><P>Table1 has column and values as:</P><P>Item no Dealer OnHandQty</P><P>1 D1 5</P><P>1 D2 10</P><P>1 D3 3</P><P>2 D1 2</P><P>2 D4 4</P><P>2 D3 5</P><P>3 D1 5</P><P>4 D3 4</P><P> </P><P> </P><P>Table2 has column and values as:</P><P>Item no Dealer </P><P>1 D1 </P><P>1 D2 </P><P>2 D1 </P><P>2 D4 </P><P>3 D1 </P><P>4 D3 </P><P> </P><P>Bridge tabel has column and values as:</P><P> </P><P>Item no Description</P><P>1 Cycle</P><P>2 ABC</P><P>3 XYZ</P><P>4 Pen</P><P> </P><P> </P><P>I want DAX to calculate sum of OnHandQty from table1 based on dealername in table2, result table such as:</P><P>i.e. For ItemNo 1  (D1+D2 OnHandQty = 15) or so...</P><P>Item no Qty</P><P>1 15 </P><P>2 6 </P><P>3 5</P><P>4 4</P><P> </P><P> </P><P>Please help me in making this kind of DAX.</P><P> </P><P> </P><P> </P>
Mon, 01 Jul 2019 09:23:05 GMT
DimpleMehta
20190701T09:23:05Z

DAX for sum of one column based on column in another table
https://community.powerbi.com/t5/DAXCommandsandTips/DAXforsumofonecolumnbasedoncolumninanothertable/mp/728336#M1614
<P>Hi all,</P><P> </P><P>Table1 and table2 has manymany relationship so I solved that relationship by creating bridge table.</P><P>So I have 3 tables: table1, table2, bridge table</P><P> </P><P>Table1bridge has Many to 1 relationship</P><P>Table2bridge has Many to 1 relationship</P><P> </P><P>Table1 has column and values as:</P><P>Item no Dealer OnHandQty</P><P>1 D1 5</P><P>1 D2 10</P><P>1 D3 3</P><P>2 D1 2</P><P>2 D4 4</P><P>2 D3 5</P><P>3 D1 5</P><P>4 D3 4</P><P> </P><P> </P><P>Table2 has column and values as:</P><P>Item no Dealer </P><P>1 D1 </P><P>1 D2 </P><P>2 D1 </P><P>2 D4 </P><P>3 D1 </P><P>4 D3 </P><P> </P><P>Bridge tabel has column and values as:</P><P> </P><P>Item no Description</P><P>1 Cycle</P><P>2 ABC</P><P>3 XYZ</P><P>4 Pen</P><P> </P><P> </P><P>I want DAX to calculate sum of OnHandQty from table1 based on dealername in table2, result table such as:</P><P>i.e. For ItemNo 1  (D1+D2 OnHandQty = 15) or so...</P><P>Item no Qty</P><P>1 15 </P><P>2 6 </P><P>3 5</P><P>4 4</P><P> </P><P> </P><P>Please help me in making this kind of DAX.</P><P> </P><P> </P><P> </P>
Mon, 01 Jul 2019 09:23:05 GMT
https://community.powerbi.com/t5/DAXCommandsandTips/DAXforsumofonecolumnbasedoncolumninanothertable/mp/728336#M1614
DimpleMehta
20190701T09:23:05Z

Re: DAX for sum of one column based on column in another table
https://community.powerbi.com/t5/DAXCommandsandTips/DAXforsumofonecolumnbasedoncolumninanothertable/mp/728408#M1618
<P><LIUSER uid="141349"></LIUSER> </P>
<P> </P>
<P>Try this MEASURE</P>
<P> </P>
<PRE>Measure =
CALCULATE (
SUM ( Table1[OnHandQty] ),
INTERSECT ( VALUES ( Table1[Dealer] ), VALUES ( Table2[Dealer] ) )
)
</PRE>
<P> </P>
<P>or this one</P>
<P> </P>
<PRE>Measure 2 =
CALCULATE (
SUM ( Table1[OnHandQty] ),
TREATAS ( VALUES ( Table2[Dealer] ), Table1[Dealer] )
)
</PRE>
Mon, 01 Jul 2019 10:45:40 GMT
https://community.powerbi.com/t5/DAXCommandsandTips/DAXforsumofonecolumnbasedoncolumninanothertable/mp/728408#M1618
Zubair_Muhammad
20190701T10:45:40Z

Re: DAX for sum of one column based on column in another table
https://community.powerbi.com/t5/DAXCommandsandTips/DAXforsumofonecolumnbasedoncolumninanothertable/mp/728413#M1619
<P><LIUSER uid="42973"></LIUSER> Thanku so much!! It works!! Can you please explain this Treatas DAX littlebit.</P>
Mon, 01 Jul 2019 10:55:28 GMT
https://community.powerbi.com/t5/DAXCommandsandTips/DAXforsumofonecolumnbasedoncolumninanothertable/mp/728413#M1619
DimpleMehta
20190701T10:55:28Z

Re: DAX for sum of one column based on column in another table
https://community.powerbi.com/t5/DAXCommandsandTips/DAXforsumofonecolumnbasedoncolumninanothertable/mp/728422#M1620
<P><LIUSER uid="141349"></LIUSER> </P>
<P> </P>
<P>Following is a very useful article to understand TREATAS<BR /><BR /><A href="https://www.sqlbi.com/articles/propagatefiltersusingtreatasindax/" target="_self">https://www.sqlbi.com/articles/propagatefiltersusingtreatasindax/</A></P>
<P> </P>
<P>Crux is that TREATAS can be used to filter a column/Table using values of an unrelated/indirectly related table<BR />INTERSECT also does a very similar job</P>
<P> </P>
<P> </P>
Mon, 01 Jul 2019 11:09:26 GMT
https://community.powerbi.com/t5/DAXCommandsandTips/DAXforsumofonecolumnbasedoncolumninanothertable/mp/728422#M1620
Zubair_Muhammad
20190701T11:09:26Z