Help with Calculated Column

[Alicia_Anderson]

I created a simple Measure that returns the results I expect in a Table.   However, I need to be able to use the Measure in a Legend so I am trying to convert it to a Calculated Column instead. 

 

What I am trying to do:

I need to sum the NoteCount for each customer_id.  If NoteCount<200, set Patient = "Not Seeing Patients", else "Seeing Patients".  I need to show the distinct count of Customers based on Patient.

These are the measures I created:

Note_Cnt = Sum(Note_Usage[NoteCount])

Patient = IF([Note_Cnt]>=200,"Seeing Patients","Not Seeing Patients")

This is a table showing the correct results:

Data looks like this:  (Note:  There are many rows of data for a Customer.)

Cust    Name    NoteCount

ABC     John      5

ABC     Jim        2

ABC     Alice     55

XYZ     Dave     16

XYZ     Todd     45

I can't seem to get the formula correct for the calculated column.   Please help.  Thanks. 

[Alicia_Anderson]

Outstanding.  The latter gave me what I needed in one column but I kind of like the idea of having one colmun to calculate the customer total and one column to set the Patient field based on that (which I was able to now do).   Any negative to using two columns or it is better to combine into one? 

[selimovd]

Hey @Alicia_Anderson ,

you can do it in one or two column, there is not much difference.

Best regards

Denis

[selimovd · Accepted solution]

Hey @Alicia_Anderson ,

the following calculated column should give you the correct total value for each Cust:

Note_Cnt Column =
CALCULATE(
    SUM( Note_Usage[NoteCount] ),
    ALLEXCEPT(
        Note_Usage,
        Note_Usage[Cust]
    )
)

For the next step just make sure that you take the MAX and not the SUM per Customer.

Or you skip the step and get everything in one calculated column:

Patient Column =
VAR vNoteCount =
    CALCULATE(
        SUM( Note_Usage[NoteCount] ),
        ALLEXCEPT(
            Note_Usage,
            Note_Usage[Cust]
        )
    )
RETURN
    IF(
        vNoteCount >= 200,
        "Seeing Patients",
        "Not Seeing Patients"
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic