Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

problem in removing Subtotal

I have used the below formula for removing the Subtotals. But for Company with single row the subtotal is not removing.

Similarly for the CAP_TYPE column if 2 rows has large then the TOTAL is large. it should be empty.

 

Formula for removing Sub_Total is 

M_O/S SHARES (m) = IF(HASONEVALUE('Merge2-Used for Earnings Monitor Tbale Format'[Os_Shrs_m]), VALUES('Merge2-Used for Earnings Monitor Tbale Format'[Os_Shrs_m]), BLANK())

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    Please create the measures as below to work on it.

    A = IF([M_CAP TYPE] = "Large",1,0)

    A1 = SUMX('Merge2-Used for Earnings Monitor Tbale Format',[A])
    A2 = IF(ISINSCOPE('Merge2-Used for Earnings Monitor Tbale Format'[CONAME]),[M_CAP TYPE],IF([A1]>=2,"Large",BLANK()))

     

6 Replies

  • vjnvinod's avatar
    vjnvinod
    Impactful Individual
    6 years ago

     

    Anonymous 

    use "table" instead of "Matrix" (from the visualization pane)

    if the data is loaded like this, then use power query and remove those rows

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous  Please select matrix click on format pane and turn off the row subtotal option

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous  i need Subtotal for limited column. so that is why i am removing subtotal for the columns which i needed.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    If and ISINCOPE function should help you in your scenario. Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

     

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Please create the measures as below to work on it.

    A = IF([M_CAP TYPE] = "Large",1,0)

    A1 = SUMX('Merge2-Used for Earnings Monitor Tbale Format',[A])
    A2 = IF(ISINSCOPE('Merge2-Used for Earnings Monitor Tbale Format'[CONAME]),[M_CAP TYPE],IF([A1]>=2,"Large",BLANK()))