Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX Function for Consolidated Calculation

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you. I will do it this way.