to sum distinct values

[Anonymous]

Hi all, 

could you please help me with the following question?

I have a table: 

and I calculate distinct projectID: 

I know how it works, but users ask for summarizing values in total.

in other words it is supposed to have 5 (1+2+2) instead of 3 in total. 

Could you please come up with any idea how I can calculate it? 

I shared my PowerBI test data  . 

Thank you for your support!

BR, 

Valeria

[v-lionel-msft]

Hi @Anonymous ,

Please create a measure like this.

Measure 2 = 
CALCULATE(
    COUNT(prdoctypes[projectID]),
    ALLEXCEPT( prdoctypes, prdoctypes[doctype])
)

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lionel-msft , 

Thank you fo your support!

It works, but resource filter is supposed to work.

I added new values for testing and when I use your calculations, a filter is frozen

Can you see above differences in 'measure 2'? It appears the same at any time.

Could you please provide any options how to keep other filters? 

BR, 

Valeria

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please try this formula. Or refer to the .pbix file.

Measure 2 = 
VAR x = 
CALCULATE(
    COUNT(prdoctypes[projectID]),
    FILTER(
        prdoctypes,
        prdoctypes[doctype] = MAX( prdoctypes[doctype])
    )
)
RETURN
IF(
    HASONEFILTER(prdoctypes[projectID]),
    x,
    COUNT(prdoctypes[projectID])
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.