Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

filter a summarize table

Hi all, I am trying to filter my SUMMARIZE table to only where the Contract Status = "Active" in table1

 

This is my current SUMMARIZE table:

 

Summarize Table = SUMMARIZE('table1, 'table1'[ID], "Profiles", MAX('table1'[Profiles]), "Additional Storage", SUM('table1'[Additional Storage]))

 

I want to add to this formula to only summarize the values where the contract status = "Active" in the original table (table1) - how would I do this?

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    According to my understanding, you want to filter a summarized table when the Contract Status equals to "Active" , right?

    You could use the following formula:

    Summarize Table =
CALCULATETABLE (
    SUMMARIZE (
        'Table1',
        'Table1'[ID],
        "Profiles", MAX ( 'Table1'[Profiles] ),
        "Additional Storage", SUM ( 'Table1'[Additional Storage] )
    ),
    'Table1'[Contract Status ] = "Active"
)

    My visualization looks like this:

    Is the result what you want? If not, please upload some data samples and expected output.

    Please do mask sensitive data before uploading.

     

    Best Regards,

    Eyelyn Qin

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

     

    I would write :  

    Summarize Table = SUMMARIZE(

    FILTER('table1', NOT('table1'[Contract Status] = "Active"))

     , 'table1'[ID], "Profiles", MAX('table1'[Profiles]), "Additional Storage", SUM('table1'[Additional Storage]))

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

    Regards,
    Pranit

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      Anonymous try this expression

       

      Summarize Table = 
SUMMARIZE(
FILTER( 'table1', 'table'[Contract Status]="Acitve" ),
'table1'[ID], 
"Profiles", MAX('table1'[Profiles]), 
"Additional Storage", SUM('table1'[Additional Storage])
)

      I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

      ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    According to my understanding, you want to filter a summarized table when the Contract Status equals to "Active" , right?

    You could use the following formula:

    Summarize Table =
CALCULATETABLE (
    SUMMARIZE (
        'Table1',
        'Table1'[ID],
        "Profiles", MAX ( 'Table1'[Profiles] ),
        "Additional Storage", SUM ( 'Table1'[Additional Storage] )
    ),
    'Table1'[Contract Status ] = "Active"
)

    My visualization looks like this:

    Is the result what you want? If not, please upload some data samples and expected output.

    Please do mask sensitive data before uploading.

     

    Best Regards,

    Eyelyn Qin