Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@rax99 Your expected output wasn't clear. Could you please post the expected output data as well as per sample input data.
Proud to be a PBI Community Champion
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.
@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
Proud to be a PBI Community Champion
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |