Help with grouped sum

[Anonymous]

I will appreciate a quick help here 

In this table (table A). Confidence score is not summerised.

And no grouping.

Link to csv:   https://drive.google.com/file/d/1RWPdBo8_BcGwFILGRUtBe9x9p8XAWgc8/view?usp=sharing

I need to create a measure of count of ConsumerIdTaskcodes  (Grouped by ConsumerIdtaskcode) where Confidencescore  (of each group) > 71.   

The result desired is  6 - based on summed/aggregated grouping. 

But I keep getting 2 (meaning it is checking raw data before aggregation.

Please assume you are working with Table A

Her is my measure:

*Total ConsumerTask codes grouped GROUP =

CALCULATE(DISTINCTCOUNT(('report ConsumerTaskCodes'[ConsumerIDTaskCode])), FILTER('report ConsumerTaskCodes','report ConsumerTaskCodes'[*Total confidence score Taskcode Temp] > 71))

Thank for your help.

@sevenhills :  I made the question clearer.

[mahoneypat · Accepted solution]

You can use a measure like this in a card visual to get your desired result.

Count Over 71 =
COUNTROWS (
    FILTER (
        VALUES ( 'report ConsumerTaskCodes'[ConsumerIdTaskcode] ),
        CALCULATE ( SUM ( 'report ConsumerTaskCodes'[confidenceScore] ) ) > 71
    )
)

If you already have a measre for confidence score, you can replace the CALCULATE expression with just [ConfidenceScoreMeasure]   (no CALCULATE needed if you use a measure).

Pat

[mahoneypat · Accepted solution]

You can use a measure like this in a card visual to get your desired result.

Count Over 71 =
COUNTROWS (
    FILTER (
        VALUES ( 'report ConsumerTaskCodes'[ConsumerIdTaskcode] ),
        CALCULATE ( SUM ( 'report ConsumerTaskCodes'[confidenceScore] ) ) > 71
    )
)

If you already have a measre for confidence score, you can replace the CALCULATE expression with just [ConfidenceScoreMeasure]   (no CALCULATE needed if you use a measure).

Pat

[sevenhills]

I used your TableB

and wrote the measure (nothing different than what you have)

Desired Measure = 
var _c = CALCULATE(
    DISTINCTCOUNT(TableB[ConsumerIdTaskCode]), 
    filter( TableB, TableB[ConfidenceScore] > 71)
)

RETURN IF( IsBlank(_c), 0, _c)

it worked perfectly ... 

[Anonymous]

@sevenhills 

Please work with Tale A instead.

I am working with the raw data not aggregated. Hope you understand.

[Anonymous]

I saw an accepted solution and  I applied same method based on Solved: dax group by measure - Microsoft Power BI Community

But it is not working for me.  I have exact same issue.

Here is the dax I tried

measure  =

CALCULATE(DISTINCTCOUNT('report ProducerConsumerTaskCodes'[ConsumerIdTaskcode]),

FILTER(ALLSELECTED('report ProducerConsumerTaskCodes'),'report ProducerConsumerTaskCodes'[ConsumerIdTaskcode] = MAX('report ProducerConsumerTaskCodes'[ConsumerIdTaskcode])

&& 'report ProducerConsumerTaskCodes'[submissionMonth] = MAX('report producerConsumerExpenseCodes'[submissionMonth]

)))

I am getting null as answer.

Here is my csv file https://drive.google.com/file/d/1RWPdBo8_BcGwFILGRUtBe9x9p8XAWgc8/view?usp=sharing

[sevenhills · Accepted solution]

@mahoneypat solution is the way to go. Just add zero logic for blanks if you want it. 

Your original post did not say that you want to do in Table A. Anyway,  since you said that you want to do the way in the link above, you can do like below

Desired Measure in A = 
var _ct = GROUPBY(TableA, TableA[ConsumerIdTaskcode], "Sum by ID", sumx(currentgroup(),TableA[confidenceScore]))
var _c = CALCULATE( DISTINCTCOUNT( TableA[ConsumerIdTaskcode]) , FILTER(_ct, [Sum by ID] > 71) )

RETURN IF( IsBlank(_c), 0, _c)