Forum Discussion

rmocca's avatar
rmocca
Icon for Helper I rankHelper I
5 years ago
Solved

Matrix Totals error

Hi again,

I'm dealing with this problem, and I've read almost every I can found, but without any luck.

 

I have a this measures:

CPK: Gets an number value according with a lot of data points.

 

CPKCategory = var thisCPK = [CPK]
var result = if (ISBLANK(thisCPK), blank(), calculate(MIN(CPKCategories[Code]), CPKCategories[Min]<= thisCPK, CPKCategories[Max]> thisCPK))
return result

 

Basically, this measure finds the CPK values in a table called CPKCategories and return the Code.

This two are working as expected.

Using this I've created this other:

 

CPKCategory1 = COUNTROWS(FILTER(VARIABLES,[CPKCategory]= 1))

 

Basically, it count the rows with CPKCategory = 1.

But, when I display this values in a matrix I'm getting this:

 

The value is correct for every month. Why the Total is showing that value? Any ideas?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi rmocca 

    Use a summarize table is a good way to solve this problem.

    Your new measure:

     

    CPK1 = if(hasonevalue(TAGVALUE[DateValue]),[CPKCategory1],sumx(TAGVALUE,values(TAGVALUE[DateValue])))

     

    In addition to jaideepnema , it seems that values function will return to distinct values in your Datevalue column.

    They may be Date Type or Text, so your meausres will return to an error.

    Build a new measure based on Measure CPKCategory.

    New Measure is like:

     

    IF(HASONEVALUE(Table[Month]),[CPKCategory],Sumx(Table,[CPKCategory]))

     

    By HASONEVALUE, Table[Month] should be the column in your matrix column field. If you use date hierachy, you can add an calcualted column by Month function. Then use these month in matrix column field. So all month will return to the result of [CPKCategory1], and Matrix Total is not in Month so it return to the false result. Due to I don't know your data model, you may update Sumx(Table,[CPKCategory]) in your measure. It should be a measure to calculate the total for all month directly.  In your sample it should be 6+5+3+8 = 22.

     

    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. 

7 Replies

  • jaideepnema's avatar
    jaideepnema
    Icon for Solution Sage rankSolution Sage
    5 years ago

    rmocca  have you tried using Sumx with the month name ? somewhat like this 

     

    if(hasonevalue(month),CPKCategory1,sumx(table name,values(<month column))

     

    Please accept this as a solution if this help..Appreciate a Kudos !!

    If not please share a sample file to check the same 

    • rmocca's avatar
      rmocca
      Icon for Helper I rankHelper I
      5 years ago

      Hi,

      I've tried but without luck, I think I'm not quite sure how to do it.

      I've tried creating a new measure:

      CPK1 = if(hasonevalue(TAGVALUE[DateValue]),[CPKCategory1],sumx(TAGVALUE,values(TAGVALUE[DateValue])))
       
      This can't be displayed. It shows an error.
       
      Or how should I implement what you've sent?
       
      Thanks again for your help.
  • rmocca's avatar
    rmocca
    Icon for Helper I rankHelper I
    5 years ago

    I've tried a lot of alternatives, that's not one of them.

    I'll try it now and let you know.

     

    Thanks

    • rmocca's avatar
      rmocca
      Icon for Helper I rankHelper I
      5 years ago

      Hi!

      I've managed to solve this using a summarized table.

      Thanks for your help!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi rmocca 

    Use a summarize table is a good way to solve this problem.

    Your new measure:

     

    CPK1 = if(hasonevalue(TAGVALUE[DateValue]),[CPKCategory1],sumx(TAGVALUE,values(TAGVALUE[DateValue])))

     

    In addition to jaideepnema , it seems that values function will return to distinct values in your Datevalue column.

    They may be Date Type or Text, so your meausres will return to an error.

    Build a new measure based on Measure CPKCategory.

    New Measure is like:

     

    IF(HASONEVALUE(Table[Month]),[CPKCategory],Sumx(Table,[CPKCategory]))

     

    By HASONEVALUE, Table[Month] should be the column in your matrix column field. If you use date hierachy, you can add an calcualted column by Month function. Then use these month in matrix column field. So all month will return to the result of [CPKCategory1], and Matrix Total is not in Month so it return to the false result. Due to I don't know your data model, you may update Sumx(Table,[CPKCategory]) in your measure. It should be a measure to calculate the total for all month directly.  In your sample it should be 6+5+3+8 = 22.

     

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi rmocca 

    Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou