Modifying a column value depending on index

[Anonymous]

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 company, we have a "controller company", which controls subcompanys from my group.

In all my database, I have data for the different subcompanies, and a specific data for "Resultado global", translating to global results, which basically aggregates (sums) all the data for each subcompany as a consolidate.

My final database looks like this:

In "Fonte" column, i have an index from which sheet in excel i extracted the data from, "Data" is the month and year, " Unidade de Negocio" are the subcompanies, including the "resultado global" I mentioned (which aggregates all others), "Contas" is the account name (from the finances reports, like cash flow, demonstratives, etc), "Real" is the actual value and "Orçado" is the planned value.

The Problem: The controller group (Resultado Global) doesn't own 100% every subcompany!  In my extraction, it counts as 100% for all, but actually, we control only 40% of "RenoVias" (a "unidade de negocio"), so I would need to only sum 40% of the values (both in "Real" and "Orçado") to "Resultado global".

I can't work this directly in the database, as I need to get as automatizated as possible, I was thinking of creating a calculated column. Basically, I need to remove 60%(100%-40%) of "RenoVias" values from Resultado Global, but I have no idea of how to do it. We also only control 99,7% from Rodoanel Oeste, so I would also need to remove 0,03% (100%-99,7%) of Rodoanel Oeste from Resultado Global, but Im satisfied if I manage to remove only the RenoVias ones.

I hope i've made my problem clear enough, any questions I would be happy to clarify.

Thanks in advance to all!

[v-eachen-msft]

Hi @Anonymous ,

You could remove the "Company ABC" rows in the query editor at first.

Then re-calculate the sum.

You could use measures:

Measure =
SUMX (
    'Table',
    IF ( 'Table'[Column3] = "Company A", 'Table'[Column5] * 0.4, 'Table'[Column5] )
)

or

Measure =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Column5] ) * 0.4,
        FILTER ( 'Table', 'Table'[Column3] = "Company A" )
    )
VAR b =
    CALCULATE (
        SUM ( 'Table'[Column5] ),
        FILTER ( 'Table', 'Table'[Column3] <> "Company A" )
    )
RETURN
    a + b

And you could use calculated columns without removing any rows. Just refer to the measures above.

[Anonymous]

@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:

1	01/01/2020	Company A	Account 1	500	755	0	0
1	02/01/2020	Company A	Account 2	700	1232	0	0
1	03/01/2020	Company A	Account 3	150	675	0	0
1	04/01/2020	Company A	Account 4	450	1401	0	0
2	01/01/2020	Company A	Account 1	640	985	0	0
2	02/01/2020	Company A	Account 2	620	941	0	0
2	03/01/2020	Company A	Account 3	630	1088	0	0
2	04/01/2020	Company A	Account 4	710	1320	0	0
1	01/01/2020	Company B	Account 1	130	1050	0	0
1	02/01/2020	Company B	Account 2	920	1372	0	0
1	03/01/2020	Company B	Account 3	540	936	0	0
1	04/01/2020	Company B	Account 4	570	845	0	0
2	01/01/2020	Company B	Account 1	420	733	0	0
2	02/01/2020	Company B	Account 2	750	1182	0	0
2	03/01/2020	Company B	Account 3	820	906	0	0
2	04/01/2020	Company B	Account 4	590	678	0	0
1	01/01/2020	Company C	Account 1	260	426	0	0
1	02/01/2020	Company C	Account 2	840	1385	0	0
1	03/01/2020	Company C	Account 3	730	1518	0	0
1	04/01/2020	Company C	Account 4	290	1140	0	0
2	01/01/2020	Company C	Account 1	500	674	0	0
2	02/01/2020	Company C	Account 2	660	952	0	0
2	03/01/2020	Company C	Account 3	780	892	0	0
2	04/01/2020	Company C	Account 4	70	326	0	0
1	01/01/2020	Company ABC	Account 1	890	2231	300	453
1	02/01/2020	Company ABC	Account 2	2460	3989	420	739,2
1	03/01/2020	Company ABC	Account 3	1420	3129	90	405
1	04/01/2020	Company ABC	Account 4	1310	3386	270	840,6
2	01/01/2020	Company ABC	Account 1	1560	2392	384	591
2	02/01/2020	Company ABC	Account 2	2030	3075	372	564,6
2	03/01/2020	Company ABC	Account 3	2230	2886	378	652,8
2	04/01/2020	Company ABC	Account 4	1370	2324	426	792

[v-eachen-msft]

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]

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 😃

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@Greg_Deckler  I've made a data sample using imaginary data, since my data is sensitive.

The thing is, i cannot do any calculations on excel, since I want it as automatic as possible, plus my data are pasted (extracting from another BI) as values only, so there are no easy way to turn them into formulas.

I need to do exactly what I described. In the data I post here, Company ABC receives values from account 1, from company A,B and C. Now, imagine I only want 40% of the values from company A into Company ABC, how would I do it using Power BI only? (PS: I cannot change how it extracts 100% from all companies, the data I receive is fixed.)

In this example, first column would be Fonte, second Data, third Unidade de Negocio, fourth Conta, fifth Real, sixth Orçado, in the real setup. I tried to make the sample larger, but is limited to 20000 characters.

1	01/01/2020	Company A	Account 1	51590	52319
1	02/01/2020	Company A	Account 2	18860	19382
1	03/01/2020	Company A	Account 3	6780	6937
1	04/01/2020	Company A	Account 4	46550	46851
2	01/01/2020	Company A	Account 1	9460	9984
2	02/01/2020	Company A	Account 2	21150	21517
2	03/01/2020	Company A	Account 3	91540	91738
2	04/01/2020	Company A	Account 4	59490	60038
1	01/01/2020	Company B	Account 1	31910	32829
1	02/01/2020	Company B	Account 2	79060	79874
1	03/01/2020	Company B	Account 3	69630	70034
1	04/01/2020	Company B	Account 4	27790	28289
2	01/01/2020	Company B	Account 1	12590	13234
2	02/01/2020	Company B	Account 2	79610	80573
2	03/01/2020	Company B	Account 3	70830	71402
2	04/01/2020	Company B	Account 4	94190	94261
1	01/01/2020	Company C	Account 1	38360	39158
1	02/01/2020	Company C	Account 2	77690	78319
1	03/01/2020	Company C	Account 3	40940	41177
1	04/01/2020	Company C	Account 4	92730	92943
2	01/01/2020	Company C	Account 1	91560	92386
2	02/01/2020	Company C	Account 2	58730	59175
2	03/01/2020	Company C	Account 3	27960	28391
2	04/01/2020	Company C	Account 4	60430	61132
1	01/01/2020	Company ABC	Account 1	121860	124306
1	02/01/2020	Company ABC	Account 2	175610	177575
1	03/01/2020	Company ABC	Account 3	117350	118148
1	04/01/2020	Company ABC	Account 4	167070	168083
2	01/01/2020	Company ABC	Account 1	113610	115604
2	02/01/2020	Company ABC	Account 2	159490	161265
2	03/01/2020	Company ABC	Account 3	190330	191531
2	04/01/2020	Company ABC	Account 4	214110	215431