Forum Discussion
Measure using a measure or create a column
Hello team,
My raw data is as below.
| Region | State | TM | ActiveCount | InactiveCount |
| West | CA | Tim | 5 | 10 |
| West | AZ | Mark | 8 | 5 |
| West | CA | Steve | 6 | 5 |
| West | CA | Mark | 5 | 5 |
| West | CO | John | 4 | 4 |
| East | NJ | Mary | 2 | 4 |
| East | NJ | Mick | 5 | 5 |
| East | MD | Jack | 8 | 10 |
The end goal, is to create a "forecast/goal-ish" columns as below. And having the State as a slicer, the end table should be
| TM | Goal | ActiveCount | Active+1 | Active+2 | Active+3 | Total |
| Jack | 25 | 8 | 9 | 11 | 12 | 40 |
| John | 25 | 4 | 5 | 5 | 6 | 20 |
| Mark | 25 | 13 | 15 | 17 | 17 | 62 |
| Mary | 25 | 2 | 2 | 3 | 3 | 10 |
| Mick | 25 | 5 | 6 | 7 | 8 | 25 |
| Steve | 25 | 6 | 7 | 8 | 9 | 30 |
| Tim | 25 | 5 | 6 | 7 | 8 | 25 |
| TOTAL | 43 | 49 | 57 | 63 | 212 |
The Goals is a straight measure.
ActiveCount is a straight count from the raw table.
Active+1 = If Activecount <15, then add 15% to ActiveCount else leave it as Active Count.
Active+2 = If Active+1 < 15, then add 15% to Active+1 else leave it as Acitve+1
Active+3 = If Active +2 < 15 then add 15% to Active+2 else leave it as Acitve+2
Now the issues i am facing.
- I can get the Active Count and Active+1 , but Active+2 measure wont let me use the Active+1 measure
- The summary/Total does not work as its a measure.
- If i convert the Active+1 into a calcualted column , then Mark shows up in two rows when CA state selected in slicer.
Any recommendations on how to get this please.
PBI5851 try these measures:
Sum Active = SUM ( TableActive[ActiveCount] ) Active +1 = VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Sum Active] ) RETURN SUMX ( __Table, IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] ) ) Active +2 = VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Active +1] ) RETURN SUMX ( __Table, IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] ) ) Active +3 = VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Active +2] ) RETURN SUMX ( __Table, IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] ) ) Total = [Sum Active] + [Active +1] + [Active +2] + [Active +3]
1 Reply
PBI5851 try these measures:
Sum Active = SUM ( TableActive[ActiveCount] ) Active +1 = VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Sum Active] ) RETURN SUMX ( __Table, IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] ) ) Active +2 = VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Active +1] ) RETURN SUMX ( __Table, IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] ) ) Active +3 = VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Active +2] ) RETURN SUMX ( __Table, IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] ) ) Total = [Sum Active] + [Active +1] + [Active +2] + [Active +3]