Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a data table that looks like this:
Total RPs | Total 360s | Total RTAs |
0 | 0 | 0 |
7 | 0 | 6 |
11 | 2 | 3 |
2 | 5 | 13 |
0 | 1 | 1 |
0 | 0 | 1 |
0 | 0 | 0 |
8 | 0 | 0 |
0 | 0 | 0 |
0 | 4 | 3 |
I want to sum across the row, then count the number of sums that are less than a target number, let's say 5.
So in this example, the calculation would result in this:
Total FPs |
0 |
13 |
16 |
20 |
2 |
1 |
0 |
8 |
0 |
7 |
and the number 5 would appear in my table (as there are 5 rows that have a result less than 5).
So the measure I started with was "If the sum across the rows is >=1 and <=4, then count the number of rows that gave that result."
This is my formula so far:
Total FPs =
(IF(SUMX(Advisors,Advisors[RTAs] + [Pathfinders] + [Financial 360s])>=1
&&
SUMX(Advisors,Advisors[RTAs] + [Pathfinders] + [Financial 360s])<=4,
DISTINCTCOUNT(Advisors[Advisor Number])).
Since it is giving me a blank, it obviously is not doing what I want it to!
Solved! Go to Solution.
Sorry, looking closer it needs to have a CALCULATE in there to work right:
measure:=COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( Advisors[Id] ), "row_total", CALCULATE( SUM( Advisors[Total RPs] ) + SUM( Advisors[Total RTAs]) + SUM( Advisors[Total 360s] ) ) ), [row_total] < 5 ) )
This also assumes you have an 'Id' column in the 'Advisors' table to uniquely identify each row.
How are you trying to visualize this? as a single number? Or in a Matrix? What are on rows/columns? anything?
Here is a shot in the dark guessing at results wanted:
measure = COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( ADVISORS[Id] ), "row_total", SUMX ( Advisors, Advisors[RTAs] + [Pathfinders] + [Financial 360s] ) ), [row_total] < 5 ) )
Yes, as a single number. I tried to paste a screenshot but won't let me!
I will try your possible solution and report back.
Thanks!
Still gave me a blank. I will begin again tomorrow.
Sorry, looking closer it needs to have a CALCULATE in there to work right:
measure:=COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( Advisors[Id] ), "row_total", CALCULATE( SUM( Advisors[Total RPs] ) + SUM( Advisors[Total RTAs]) + SUM( Advisors[Total 360s] ) ) ), [row_total] < 5 ) )
This also assumes you have an 'Id' column in the 'Advisors' table to uniquely identify each row.
That worked, thanks! How would I change it if I wanted only those with 1-4 sales (i.e. don't count those with zero)?
User | Count |
---|---|
97 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |