Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Matrix chart - show value once time

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. 

Need Help

4 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        danextian
        Super User
        1 year ago

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