Want to show sum of value based on 5 different columns from 2 different tables

[AshDil]

Hi,

I have 2 tables in which, I want to get total sum based on 5 columns. So I have tried with following formula it is throwing error.

EVALUTE(SUMMARIZECOLUMNS('TABLE1'[ORDER_NO],'TABLE1'[QUE_NO],'TABLE2'[QUE_NO],'TABLE2'[ID],'TABLE2'[CUST_ID],'TABLE1'[CUST_NAME],'TABLE1'[DATE],SUM('TABLE2'[SALES])

Please hlep me to do it.

Thanks,

AshDil

[TheoC]

Hi @AshDil 

Are you able to provide a little more detail about what you're wanting?  

If you are after the Total Sum, you can use the measure "Total Sum = SUM ( 'TABLE2'[SALES] )"

From there, all you need to do is use a Table visual from the Visualizations pane, drag the columns you want in the Table, and then drag the measure "Total Sum" at the end of the table.

Just please make sure that you have a one to many relationship (single direction) from Table1 to Table2 using the [ORDER_NO] field as the relationship.

Hope that helps 🙂

[AshDil]

Hi @TheoC ,

I have many to many relationship between table 1 and table 2.

So please guide me to achieve this.

Thanks,

AshDil

[TheoC]

Hi @AshDil what is the unique ID in your Table 1 and what is the relationship between your Table 1 and Table 2?

[AshDil]

Hi @TheoC ,

The unique column is Order_no, relation between table 1 and table 2 is many to many based on this column.

Thanks,

AshDil

[TheoC]

Hi @AshDil 

Okay, thanks for letting me know.

Now, all you need to do is use a Table visual from the Visualizations pane, drag the columns you want in the Table, and then drag the measure "Total Sum" at the end of the table.

Thanks heaps 🙂

[AshDil]

Hi @TheoC ,

I want to show that value in KPI card, the total sum value must be based on the above mentioned columns.

Sorry for troubling,Please help me if any other way to resolve it.

Thanks,

AshDil

[Anonymous · Accepted solution]

Hi @AshDil 

Do you want to calculate the total based on many different columns in two tables?

There could be only one active relationship between two tables. Here I suggest you to create a Newkey columns in two tables for calculation.

NewKey are calculated columns.

NewKey = COMBINEVALUES(",",Table1[Order_no],Table1[Column2],Table1[Column3])

NewKey = COMBINEVALUES(",",Table2[Order_no],Table2[Column2],Table2[Column3])

Measure:

Total = SUMX(FILTER(ALL(Table2),Table2[NewKey] in VALUES(Table1[NewKey])),Table2[Value]) 

Result is as below.

Best Regards,
Rico Zhou

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