Matrix chart - show value once time

[Anonymous]

I have a table F_MK with columns: ID, MKID, Dep, Tres. I also have a table F_Re with columns: ID, MKID, and Ac (General Number type). Both tables share the MKID column, and the ID columns in both tables have relationships with the Dim_Pl table, which has columns: ID and Name.

Now, I want to create a matrix where:

• Rows: MKID, Dep, Tres from the F_MK table.
• Columns: Name from the Dim_Pl table.
• Values: The sum of the Ac column from the F_Re table.

I created a measure as follows:
Total Ac = SUMX( VALUES(Dim_Pl[ID]), CALCULATE( SUM(F_Re[Ac]), TREATAS(VALUES(F_MK[MKID]), F_Re[MKID]) ) )
After creating the measure, the matrix will look like this:

But I want like this :

What I want is for each MKID, the values should only appear once (since the values are the result of the sum for a single MKID). However, right now, the values are getting duplicated. 

[danextian]

Hi @Anonymous 

It looks like you are trying to create a calculated column and not a measure. And the reason the values repeat is because the filter is on MKID only and all other columns are excluded.

try:

SUMX (
    // Filter rows in table1
    FILTER (
        table1,
        // Condition 1: Matching MKID value from table2
        table1[MKID]
            = EARLIER ( table2[MKID] ) // Condition 2: Matching Dep value from table2
            && table1[Dep] = EARLIER ( table2[Dep] ) // Condition 3: Matching ID value from table2
            && table1[ID] = EARLIER ( table2[ID] ) // Condition 4: Matching Tres value from table2
            && table1[Tres] = EARLIER ( table2[Tres] )
    ),
    // Sum the [Value] column for the filtered rows
    [Value]
)

Table2 is where will be createing this calculated column.

[Anonymous]

Hi danextian, Thank you for your appreciate time . I don't understand Table 1 and table 2 is what you mean. Two pictures that I post is I assume that the Matrix chart that I want to create. Information about the table is here:
"

I have a table F_MK with columns: ID, MKID, Dep, Tres. I also have a table F_Re with columns: ID, MKID, and Ac (General Number type). Both tables share the MKID column, and the ID columns in both tables have relationships with the Dim_Pl table, which has columns: ID and Name.

Now, I want to create a matrix where:

• Rows: MKID, Dep, Tres from the F_MK table.
• Columns: Name from the Dim_Pl table.
• Values: The sum of the Ac column from the F_Re table."
  
  

Thank you.

[danextian]

I was referring to this table for Table2. This is where you will create the calculated column. Table1 is the other fact table.

[Anonymous]

No, I mean Table 2 and Tabe 1 is not a table , its is the Matrix CHART  I want to create, Table 2 is Matrix CHART that I want to create like this.