Forum Discussion
Helper IICurrent category count subtract with remaining categories count
Hi Guys,
I have the data as below
NamescyclesDate
| A | -1 | 01-01-2020 |
| A | -1 | 02-01-2020 |
| A | 0 | 03-01-2020 |
| A | 1 | 04-01-2020 |
| A | 1 | 05-01-2020 |
| A | 1 | 05-01-2020 |
| A | 1 | 06-01-2020 |
| A | 1 | 07-01-2020 |
| A | 1 | 08-01-2020 |
| A | 1 | 08-01-2020 |
| A | 1 | 09-01-2020 |
| A | 2 | 10-01-2020 |
| A | 2 | 11-01-2020 |
| A | 3 | 11-01-2020 |
| A | 4 | 11-01-2020 |
| B | 0 | 01-01-2020 |
| B | 0 | 02-01-2020 |
| B | 1 | 03-01-2020 |
| B | 1 | 04-01-2020 |
| B | 1 | 05-01-2020 |
| B | 1 | 05-01-2020 |
| B | 1 | 06-01-2020 |
| B | 1 | 08-01-2020 |
| B | 1 | 08-01-2020 |
| B | 1 | 09-01-2020 |
| B | 1 | 10-01-2020 |
| B | 2 | 11-01-2020 |
| B | 2 | 11-01-2020 |
| B | 3 | 11-01-2020 |
| B | 3 | 11-01-2020 |
| B | 3 | 11-01-2020 |
| C | -1 | 01-01-2020 |
| C | 0 | 02-01-2020 |
| C | 0 | 03-01-2020 |
| C | 0 | 04-01-2020 |
| C | 1 | 05-01-2020 |
| C | 1 | 05-01-2020 |
| C | 1 | 06-01-2020 |
| C | 1 | 08-01-2020 |
| C | 1 | 08-01-2020 |
| C | 1 | 09-01-2020 |
| C | 1 | 10-01-2020 |
| C | 2 | 11-01-2020 |
| C | 3 | 11-01-2020 |
I am populating this data in a matrix visual which will look like
Now my requirement is that from column 1 to 4
the current value needs to be subtracted from the sum of next columns.
for example:
For A i have 8 in [1] column, 8 - (2+1+1) = 4 will be displayed in [1] instead of 8. so for A the values will be 2,1,4,0,0,1
The values of A are 2,1,8,2,1,1 updated values will be 2,1,8-(2+1+1),2-(1+1),1-1,1
the values of 0 and -1 won't change only substraction will be done for columns 1 to 4
Expected output
I am attaching sample pbix
https://drive.google.com/file/d/13lB30RroIeOKyiptiif181iWvD-eFyWn/view?usp=sharing
Please help me on this guys..!!!
Thanks in Advance!
https://www.dropbox.com/s/xgf8cztetxl562t/Sample.pbix?dl=0
Expected Output : =
VAR currentcycle =
MAX ( 'Dim Cycles'[cycles] )
VAR countrownormal =
COUNTROWS ( Table1 )
VAR countrowcondition =
countrownormal
- CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( ALL ( 'Dim Cycles' ), 'Dim Cycles'[cycles] > currentcycle )
)
RETURN
IF ( currentcycle <= 0, countrownormal, countrowcondition )
11 Replies
- Jihwan_Kim
Super User
https://www.dropbox.com/s/xgf8cztetxl562t/Sample.pbix?dl=0
Expected Output : =
VAR currentcycle =
MAX ( 'Dim Cycles'[cycles] )
VAR countrownormal =
COUNTROWS ( Table1 )
VAR countrowcondition =
countrownormal
- CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( ALL ( 'Dim Cycles' ), 'Dim Cycles'[cycles] > currentcycle )
)
RETURN
IF ( currentcycle <= 0, countrownormal, countrowcondition )
HemanthV- HemanthV
Hi Jihwan_Kim ,
The solution worked but the column totals are showing wrong
instead of 8,8,10 it is displaying 15,16,13. can you help me with this
Thanks in advance!
- Jihwan_Kim
Thank you for the feedback.
Please check the link down below.
https://www.dropbox.com/s/xgf8cztetxl562t/Sample.pbix?dl=0