Forum Discussion
Calculating a total in one step with assessing multiple rows at the same time
I have data that looks like this. Customers can get multiple ratings. If at least one of those ratings fulfills the logic, that customer is counted once as being positively rated. I'm not counting the number of ratings. If a customer has AT LEAST one, it counts as one. If they have ten positive ratings, it still counts as one at the customer level.
The end result of this data is a distinct count of Customer ID. The metric is "Number of customers that received one or more positive ratings".
What I'm currently doing is three steps.
Step One = IF(([Rating]= "Good"
&&
Risk= "Moderate Risk") ||
([Rating]= "Good" &&
Risk= "High Risk"),
1
, BLANK())
Step Two = IF([Step One] > 0, [Customer ID], BLANK())
Then doing a distinct count of Step Two.
Is there a simpler way to do this in one step?
Please try the measure below:
Customers with Positive Rating = CALCULATE ( DISTINCTCOUNT ( Table1[Customer ID] ), Table1[Rating] = "Good", Table1[Risk] IN { "Moderate Risk", "High Risk" } )
4 Replies
- cengizhanarslan
Super User
Please try the measure below:
Customers with Positive Rating = CALCULATE ( DISTINCTCOUNT ( Table1[Customer ID] ), Table1[Rating] = "Good", Table1[Risk] IN { "Moderate Risk", "High Risk" } ) - Shai_Karmani
Yes, you can collapse this into a single measure with CALCULATE plus DISTINCTCOUNT:
Customers Positively Rated =
CALCULATE (
DISTINCTCOUNT ( YourTable[Customer ID] ),
YourTable[Rating] = "Good",
YourTable[Risk] IN { "Moderate Risk", "High Risk" }
)
CALCULATE filters the table down to rows where Rating is Good and Risk is either Moderate or High, then DISTINCTCOUNT counts each Customer ID only once. A customer with ten qualifying rows still counts as one, which matches what you described, and you can drop the Step One and Step Two helper columns entirely.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai Karmani
- mickey64
For your reference.
Step 0: I use these data below.
Step 1: I make a measure and a matrix below.
Count = CALCULATE(DISTINCTCOUNT('DATA'[Customer ID]),FILTER('DATA',([Rating]="Good"&&[Risk]="Moderate Risk")||([Rating]="Good" && [Risk]= "High Risk")))
- Ashish_Mathur
Hi,
Please share data in a format that can be pasted in an MS Excel file.
