Forum Discussion
DAX Calculation for Aggregated values inside single table
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 :smileyfrustrated:
Hi Anonymous,
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
5 Replies
Anonymous Your expected output wasn't clear. Could you please post the expected output data as well as per sample input data.
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.
Anonymous 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)*100Test20Calc2 = VAR _DistinctCount = COUNTROWS(FILTER(Test20Measures,Test20Measures[AgentCount]=1)) VAR _TotalRows = COUNTROWS(Test20Measures) RETURN (_DistinctCount/_TotalRows)*100
