Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sum some rows in matrix

Hello!!

I have this issue and Im not sure if is possible to do in pbi

 

I have a matrix like this

 
 

I want to sum, only some of the categories, for example in this case, I only want to sum books and sport, and furniture only show the subcategory, with the data, but in the row of the furtinure that can appear blank.

Is possible to do that?

Thanks for the help

 

Monica

matrix
sum

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    You can make use of the HASONEVALUE function to get what you're looking for, depending on how your data is set up.

    The function takes a column name as its argument, you will want to use the column name that contains the subcategories. Your measure might look like this:

    Measure = 
IF ( 
NOT(HASONEVALUE[table[subcategory])) && table[category] = "Furniture",
BLANK(),
CALCULATE ( 
SUM( table[value] ) )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous, in a measure that does not have an iteration in it, you cannot compare a naked column to a value, so this measure of yours should error out. Also CALCULATE around SUM is not required. Moreover, this measure is syntactically incorrect.

      Best
      D

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks for the comment, is correct, I cant use this solution, im still looking for a solution for this.