Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Problem with count and distinctcount measure

Hi

I need a measure/calculated column that counts Red as distinctcount from the X column and the Blue as count from the Y column.

 

This is my data:
 

 

Color                      X                Y

Red                         1              200

Blue                        1              200

Red                         1              200

Blue                        1              200

Red                         1              200

 

 

I made a measure which gives me the right total number but not the right division of Red and Blue. So my matrix looks like this:

 

                Number of Transactions

Red                        6

Blue                       6

Total                      6

 

 

I want the matrix to look like this:

                Number of Transactions

Red                         1

Blue                        5

Total                       6

 

Thanks, 
Robert 

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Re = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Color],
        "Result",
        IF(
            [Color]="Red",
            CALCULATE(
                DISTINCTCOUNT('Table'[X]),
                FILTER(
                    ALL('Table'),
                    'Table'[Color]=EARLIER('Table'[Color])
                )
            ),
            COUNTROWS(
                ALL('Table')
            )
        )
    ),
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , count(Table[y]) should give that. Looking at this data can not make much sense

  • nvprasad's avatar
    nvprasad
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi,

    I hope you are taking colors from different tables and transactions from a different table.  Please check whether these two tables having relationships or not.

     

    Ideally, if it has relationships it should display expected results. Incase if you are having a chain of relationships then please make sure relationships are both directional.

     

    Appreciate a Kudos! 🙂
    If this helps and resolves the issue, please mark it as a Solution! 🙂

    Regards,
    N V Durga Prasad

     

     

  • themistoklis's avatar
    themistoklis
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous 

     

    You can try the following measure: 

     

    Measure = 
SUMX (
    Table_1,    -- Table Name
    IF (
        Table_1[Color] = "Red",
        CALCULATE(DISTINCTCOUNT ( Table_1[X] )),
        CALCULATE(COUNT ( Table_1[Y] ))
    )
)
  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Re = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Color],
        "Result",
        IF(
            [Color]="Red",
            CALCULATE(
                DISTINCTCOUNT('Table'[X]),
                FILTER(
                    ALL('Table'),
                    'Table'[Color]=EARLIER('Table'[Color])
                )
            ),
            COUNTROWS(
                ALL('Table')
            )
        )
    ),
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

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