DAX Function for Consolidated Calculation

[Anonymous]

Hello everyone,

I need to create a measure to calculate the following operation:
Imagine a product and we have linked two more products, being: Cod. 656 - Cod. 657 - Cod. 658. I need to consolidate (sum consumption) into a single code (may be the first code 656 - reference).

I have two tables where the data is arranged as follows:

Table 1
Cod | Consumption | Group
656 | 10                    | A
657 | 15                    | B
658 | 05                    | C

Table 2
Cod 1 | Cod 2 | Cod 3 | Total consumption
656     | 657    | 658     | 30 <==== the dax function should calculate here

When generating a visualization or a panel, I will filter by “Group A” but the total consumption value should be displayed from all “Groups A / B / C”.

Is there any DAX function or other way to do it?

Gabriel

[Anonymous]

Thank you. I will do it this way.

[Anonymous]

First, in Power Query you have to transform your table into this form

Table 1
Cod | Consumption | Group|LinkId
656 | 10                    | A       |656
657 | 15                    | B       |656
658 | 05                    | C       |656

LinkId should be a hidden field. Second, you can create a measure 

[Total Consumption] =
	calculate(
		sum ( 'Table 1'[Consumption] ),
		all ( 'Table 1' ),
		values ( 'Table 1'[LinkId] )
	)

Best

D.