Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Modifying a column value depending on index

Hello and good morning to everyone! I have the following problem: Im developing a report for my superiors, so far so good, but I discovered I need to make one little change to everything. In my co...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
6 years ago

v-eachen-msft Thanks for your answer! Sadly, it is not that simple. I can create a calculated column retaining 60% of the value from "Company A", and maintain all the rest. I cannot remove "Company ABC" because I need it as is extracted, just need to change this one value. I have two points to make:

First: The entire column of "Company A" must remain the same, I still need its full values if I want to analyse "Company A" by itself. Only when I need to analyse "Company ABC", is when I need it to count only 40% of "Company A".

Second: As noted, if I create a calculated column, identical to the original, but only with 60% of the values from "Company A", in the the table, this column would have different values only in the "Company A" rows, correct? All the rest would be identical. If I could place this 60% "Company A" values, on the same row as "Company ABC" values, side by side, I think it would be possible to simply subtract one from another, and I would get my result! Problem is: How do I write a calculated column, to give me 60% "Company A" results, on "Company ABC" rows? And if all the other values from Company A, B and C could be 0, the result would be perfect, cause it wouldn't impact the original column.

 

Maybe something like this:

 
101/01/2020Company AAccount 150075500
102/01/2020Company AAccount 2700123200
103/01/2020Company AAccount 315067500
104/01/2020Company AAccount 4450140100
201/01/2020Company AAccount 164098500
202/01/2020Company AAccount 262094100
203/01/2020Company AAccount 3630108800
204/01/2020Company AAccount 4710132000
101/01/2020Company BAccount 1130105000
102/01/2020Company BAccount 2920137200
103/01/2020Company BAccount 354093600
104/01/2020Company BAccount 457084500
201/01/2020Company BAccount 142073300
202/01/2020Company BAccount 2750118200
203/01/2020Company BAccount 382090600
204/01/2020Company BAccount 459067800
101/01/2020Company CAccount 126042600
102/01/2020Company CAccount 2840138500
103/01/2020Company CAccount 3730151800
104/01/2020Company CAccount 4290114000
201/01/2020Company CAccount 150067400
202/01/2020Company CAccount 266095200
203/01/2020Company CAccount 378089200
204/01/2020Company CAccount 47032600
101/01/2020Company ABCAccount 18902231300453
102/01/2020Company ABCAccount 224603989420739,2
103/01/2020Company ABCAccount 31420312990405
104/01/2020Company ABCAccount 413103386270840,6
201/01/2020Company ABCAccount 115602392384591
202/01/2020Company ABCAccount 220303075372564,6
203/01/2020Company ABCAccount 322302886378652,8
204/01/2020Company ABCAccount 413702324426792
 
v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi Anonymous ,

 

Accoding to your expect result, I used RANKX() functino to create some groups based on an extra index column( you need to create it in the query editor ).

rank =
RANKX (
    FILTER ( 'Table', 'Table'[Column3] IN { "Company A", "Company ABC" } ),
    'Table'[Index],
    ,
    ASC,
    DENSE
)

Then use EARLIER() function return the result.

Result 1 =
VAR a =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Column5], 1 ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[rank] ) - 8 )
    ) * 0.6
RETURN
    IF ( 'Table'[Column3] = "Company ABC", a, 0 )

Here is the final table.

Here is my test file for your reference.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello v-eachen-msft  Thank you for helping me out! The code is beggining to look like exactly what I need, in the example it works just fine! I still need something more to it, because I have two problems with this code on my real data:

     

    I can't have something like 'Table'[rank] - 8 in the code, because its a growing table, positions might change. Sometimes, there are company A rows followed by company B, and then A again (although this could be changed when classifying a column by asc,desc), but still, the -8 would be a problem, I need it to be as automatic as possible, as every month, new data is added, and it already has over 100.000 rows

     

    One other thing, "Company ABC" might have accounts that Company A doesn't! This happens if other companies have despenses/expenditures/profits on other things than Company A (although, most of the accounts are the same)

     

    I have no problem creating an index in the query, maybe if we used another function other than RankX, it would be better to do what I need? I dunno, a column from 1 to X indexing every row in company A, 0 for others, and from X to Y indexing every row in Company ABC? Or a column having matching index for identical Accounts and Fontes for Company A and Company ABC? Im really having trouble trying to work this out, but im very thankful for ur help so far 😃