DAX Calculation for Aggregated values inside single table

[rax99]

Hi all,

I need a little DAX help!

I have the following table structure:

Customer	NID	AgentCount
AAA	15897457	1
AAA	67845217	4
AAA	58579248	3
AAA	82465752	4
BBB	55882471	2
BBB	84511198	1
CCC	93970254	2
CCC	15897457	2
CCC	67845217	2
CCC	58579248	2
CCC	82465752	2
CCC	76882471	2
CCC	84511198	2
DDD	80970254	1
EEE	95897457	2
FFF	47845217	3
FFF	38579248	4
FFF	12465752	3
FFF	25882471	2
FFF	34681197	3
FFF	93915454	1
FFF	46578576	4

(NID is unique)

I need help with 2 calculations:

Calc1 = find/count up where only 1 instance of the customer / All Customers

Calc2 = find/count NIDs where agentcount = 1/ ALL NIDs

This is really making my head scratch as it appears to be quite simple, but I cant seem to figure it out

[PattemManohar]

@rax99 Your expected output wasn't clear. Could you please post the expected output data as well as per sample input data.

[rax99]

So just to clarify, from the sample table above.

Calc1:

- There are just 2 customers where there is only a single instance of them, "DDD" and "EEE".

- In total there are 6 unique customers, AAA, BBB, CCC, DDD, EEE, and FFF.

- Expected result is (2/6) = 33.33%

Calc 2:

-There are 4 NIDs that have a AgentCount of 1, NIDs. 15897457, 84511198, 80970254, 93915454

- There are 22 NIDs (or 22 records in total)

- Expected result is (4/22) = 18.18%

I hope that helps.

[PattemManohar]

@rax99 Thanks for making it clear. Here are the two measures you are looking for.

Test20Calc1 = 
VAR _TotalUniqCount = DISTINCTCOUNT(Test20Measures[Customer])
VAR _SingleCustCount = COUNTROWS(FILTER(SUMMARIZECOLUMNS(Test20Measures[Customer],"Cnt",COUNTROWS(Test20Measures)),[Cnt]=1))
RETURN (_SingleCustCount/_TotalUniqCount)*100 

Test20Calc2 = 
VAR _DistinctCount = COUNTROWS(FILTER(Test20Measures,Test20Measures[AgentCount]=1))
VAR _TotalRows = COUNTROWS(Test20Measures)
RETURN (_DistinctCount/_TotalRows)*100

[rax99]

Thanks, the 2nd calc works fine.

However im getting error with calc1:

NormalisedDays=TableName

FCRv1 = MeasureName

Im not even using AddMissingItems() in the calculation. Any Ideas?

[v-danhe-msft · Accepted solution]

Hi @rax99,

Based on my test, you could refer below steps to get the Calc1:

Create a calculated column:

Column = CALCULATE(COUNT(Table1[Customer]),FILTER('Table1','Table1'[Customer]=EARLIER(Table1[Customer])))

Create a measure and you could get the correct result:

Calc1 = CALCULATE(COUNT(Table1[Column]),FILTER('Table1',Table1[Column]=1))/DISTINCTCOUNT(Table1[Customer])

You could also download the pbix file to have a view.

Regards,

Daniel He