Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

to sum distinct values

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...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-lionel-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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.