Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tonyusc77
Frequent Visitor

Multiply Count by Value on different tables (Relationship as many to many due to blanks<nulls>)

hi guys,

 

need some Dax help

I want to multiply the count from the transactions column on table 1 by its unique value on table 2 and get the result as in table 3 so I can show the total on a card.

Captura.PNG

 
 
 

>Thanks..

 

1 ACCEPTED SOLUTION
DataZoe
Employee
Employee

@Tonyusc77 I think there are a number of ways to go about this one.

 

1. you could create table as a calculated table:

 

Table 3 = summarizecolumns('Table 2'[Transactions Id],"Total Value",sum('Table 2'[Value])*calculate(count('Table 1'[Transactions]),'Table 1'[Transactions]='Table 2'[Transaction ID]))

 

2. you could create a relationship between Table 1 and Table 2 on the Transaction ID, then some measures:

Trans Count = count('Table 1'[Transactions])

Trans Value = sum('Table 2'[Value))

Total Value = [Trans Count] * [Trans Value]

 

you can then create a table visual and just put the Table 2 Transactions ID and the [Total Value] measure on it. 

 

3. You could even make it a calculated column in Table 2. Total Value = [Value] * calculate(countrows('Table 1'),'Table 1'[Transactions] = [Transactions ID])

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@Tonyusc77 , Try

New Table =
var _A = summarize(Table1, Table1[Transaction],"_cnt" ,count(Table1[Transaction]))
var _B = selectcolumns(Table2,"Transaction", Table2[Transaction ID], "_val",Table2[Value])
return
summarize(NATURALLEFTOUTERJOIN ( A, B ),[Transaction],"Value",sum([_cnt])*sum([_val]))

 

Refer

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

 

Thank you for your prompt response @amitchandak . I tried it but the last part wasn´t showing , the "_cnt" wasn´t displaying as an option. 

 

New Table =
var _A = summarize(Table1, Table1[Transaction],"_cnt" ,count(Table1[Transaction]))
var _B = selectcolumns(Table2,"Transaction", Table2[Transaction ID], "_val",Table2[Value])
return
summarize(NATURALLEFTOUTERJOIN ( A, B ),[Transaction],"Value",sum([_cnt])

DataZoe
Employee
Employee

@Tonyusc77 I think there are a number of ways to go about this one.

 

1. you could create table as a calculated table:

 

Table 3 = summarizecolumns('Table 2'[Transactions Id],"Total Value",sum('Table 2'[Value])*calculate(count('Table 1'[Transactions]),'Table 1'[Transactions]='Table 2'[Transaction ID]))

 

2. you could create a relationship between Table 1 and Table 2 on the Transaction ID, then some measures:

Trans Count = count('Table 1'[Transactions])

Trans Value = sum('Table 2'[Value))

Total Value = [Trans Count] * [Trans Value]

 

you can then create a table visual and just put the Table 2 Transactions ID and the [Total Value] measure on it. 

 

3. You could even make it a calculated column in Table 2. Total Value = [Value] * calculate(countrows('Table 1'),'Table 1'[Transactions] = [Transactions ID])

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

@DataZoe The table 1 has dates in it next to those transactions. How can I include them to the table I just created?

@Tonyusc77 I'm glad that you got one that worked for you!

 

to include other fields, you can do that before the "Total Value":

 

Table 3 = summarizecolumns('Table 2'[Transactions Id],'Table 1'[Date],"Total Value",sum('Table 2'[Value])*calculate(count('Table 1'[Transactions]),'Table 1'[Transactions]='Table 2'[Transaction ID]))

 

and you can include as many fields as you want there, but it will segment and do that "Total Value" calculation at that level.  If you are wanting to do more robust analysis on it with many slicer etc that are on fields available in those other table, you may consider trying the measures with relationships approach too. 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

@DataZoe Thank you for your prompt and vast response. I picked option 1.🥇

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.