Incorrect Values in Scatter Plot

[POSPOS]

Hi All,

I have a requirement to show the count of ID as lables in Scatter Plot. I have created a column for the same but the values are not working as expected.
The expected result for dept 2 is 2 but it shows as 1 in the scatter plot. 

I have created a sample report with sample data here. Could someone suggest on how this can be achieved?
Thank you.

[POSPOS]

@Anonymous  - Thank you for the solution.

While testing, I noticed that the ID Table could not be joined with the Sal Table as the departments are not same in both these tables.
We will need to get the count of each dept from the ID table and not based on the dept from Sal table.

I added a new table "Owner" as per the requirement and joined with the ID table . Could you please help with the current design, I have updated the file here

Thank you.

[Anonymous]

Hi @POSPOS ,
Based on the example data you provided, it looks like your MEASURES are being created based on the Dept of the sal table, while your ids are derived from the ID table, which results in a different contextual environment than the COLUMN you are trying to create.
You can try creating the following column under the Sal table

Count = 
CALCULATE(
    COUNT(Sal[Dept]),
    FILTER(
        ALLEXCEPT(
            Sal,
            Sal[ID]
        ),
        RELATED('ID'[Appointment Date]) >= DATE(2022,07,01)
    )
)

Count of ID = 
CALCULATE(
    COUNT(Sal[Dept]),
    ALLEXCEPT(
        Sal,
        Sal[Dept]
    ),
    Sal[Count] = 1
)

Finally the Count of id column is applied to the image

Best regards,
Albert He

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

[POSPOS]

@Anonymous  - Thank you for the solution.

While testing, I noticed that the ID Table could not be joined with the Sal Table as the departments are not same in both these tables.
We will need to get the count of each dept from the ID table and not based on the dept from Sal table.

I added a new table "Owner" as per the requirement and joined with the ID table . Could you please help with the current design, I have updated the file here

Thank you.

[Anonymous]

Hi @POSPOS ,
Create a column under Owner table

Count of department = 
CALCULATE(
    COUNT('ID'[Count of ID]),
    FILTER(
        ALLEXCEPT(
            'ID',
            'ID'[Dept]
        ),
        'ID'[Appointment Date]>= DATE(2022,07,01)
    )
)

Create a column under Sal table

Count = 
CALCULATE(
    MIN(Owner[Count of department]),
    ALLEXCEPT(
        Sal,
        Sal[Dept]
    )
)

Final output

Best regards,
Albert He

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

[POSPOS]

Hi @Anonymous  - I tried to replicate the same in the original report and I get same values for all the departments