Current category count subtract with remaining categories count

[HemanthV]

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!

@parry2k , @amitchandak 

[Jihwan_Kim · Accepted solution]

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 )

[Jihwan_Kim · Accepted solution]

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]

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]

@HemanthV 

Thank you for the feedback.

Please check the link down below.

https://www.dropbox.com/s/xgf8cztetxl562t/Sample.pbix?dl=0 

[HemanthV]

Hi @Jihwan_Kim ,

Thank you so much! But i am curious that is it possible to find the percentages for values.
I will use this percentages in another matrix.

I am using the following measure for percentage

Percentage_new =

var cnt = [Expected Output :]

var totalcnt = CALCULATE(COUNT(Table1[cycles]),ALL(Table1[cycles]))

var percentage = DIVIDE(cnt,totalcnt)*100

return

percentage

[Jihwan_Kim]

Sorry that I quite do not understand what you are looking for.

Is  [percentage_new] showing the result that you expected? 

What is your expected outcome?

[HemanthV]

@Jihwan_Kim 

I want to see percentage for the values

In the above pic
each value should be divided by total right for percentage
for example 
for "A" the percentages are 25,12.5,50,0,0,12.5 right, but i am getting wrong values.

2/8*100,  1/8*100,  4/8*100,  0/8*100,  0/8*100,  1/8*100

can you help me with this.

[Jihwan_Kim · Accepted solution]

https://www.dropbox.com/s/xgf8cztetxl562t/Sample.pbix?dl=0 

[HemanthV]

Hi @Jihwan_Kim,

The solution worked but, i am struggling to get this. currently we are showing the percentages for all values right. excluding -1 values it should consider only values from zero to 4 and that is the total, and the percentages need to be shown as below

expected output:

Thanks in advance!

[Jihwan_Kim]

https://www.dropbox.com/s/xgf8cztetxl562t/Sample.pbix?dl=0 

[HemanthV]

Hi @Jihwan_Kim ,
I need some help from you. 

I need -1 to 6 columns in one table and:

1) For -1, I need Cycle table count.

2) from 0 to 6 I need which_day table count

Thanks in advance!

[HemanthV]

Hi @Jihwan_Kim,

Thank you so much for helping me out! It Worked.

Thanks a lot..!