Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone!
I'm looking to get the sum for distinct count of ID’s. Also, I would like it to sum only for distinct count of ID’s that are within the range of greater than or equal to 10% or less than or equal to -10%.
The percentages are calculated by this measure in my pbi file: Variance Thresholds = SUMX(VALUES('Table'[Month]), [Result])
Eg of output:
ID | Last Date Updated | percentage | desired output |
2 | 1/1/20 | -11% | 1 |
2 | 1/2/20 | -20% |
|
2 | 1/4/20 | -25% |
|
5 | 1/4/20 | 15% | 1 |
7 | 1/7/21 | 10% | 1 |
9 | 1/7/21 | 25% | 1 |
desired result:
So the scorecard visual count result would be: 4
This is my power bi file:
https://drive.google.com/drive/folders/160P9RckiEDHhMvOSijML4bFtEQnaJx9s?usp=sharing
I've tried 2 different simple test scripts (in the file) which didn't work at all:
1. test 5% var = IF([Variance Thresholds] >= 0.05, count([ID]), 0)
2. score card for project ID's variating by 10% = SUMX(VALUES('Table'[Month]), CALCULATE(DISTINCTCOUNT('Table'[Variance Thresholds])))
Thank you all so much in advance for sharing your expert advice!
@Aj24 , Try like
Sumx(summarize(Table, Table[ID], Table[Last Date], "_1", IF([Variance Thresholds] >= 0.05, 1, 0)),[_1])
thanks so much @amitchandak !! great idea!! Unfortunately, I tried this method and it didn't work... hoping for an alternative way of approaching this 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |