filter a summarize table

[Anonymous]

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?

[v-eqin-msft · Accepted solution]

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

[pranit828]

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]

@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.⚡