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 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!

Need Help
Show and Tell

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

      101/01/2020Company AAccount 15159052319
      102/01/2020Company AAccount 21886019382
      103/01/2020Company AAccount 367806937
      104/01/2020Company AAccount 44655046851
      201/01/2020Company AAccount 194609984
      202/01/2020Company AAccount 22115021517
      203/01/2020Company AAccount 39154091738
      204/01/2020Company AAccount 45949060038
      101/01/2020Company BAccount 13191032829
      102/01/2020Company BAccount 27906079874
      103/01/2020Company BAccount 36963070034
      104/01/2020Company BAccount 42779028289
      201/01/2020Company BAccount 11259013234
      202/01/2020Company BAccount 27961080573
      203/01/2020Company BAccount 37083071402
      204/01/2020Company BAccount 49419094261
      101/01/2020Company CAccount 13836039158
      102/01/2020Company CAccount 27769078319
      103/01/2020Company CAccount 34094041177
      104/01/2020Company CAccount 49273092943
      201/01/2020Company CAccount 19156092386
      202/01/2020Company CAccount 25873059175
      203/01/2020Company CAccount 32796028391
      204/01/2020Company CAccount 46043061132
      101/01/2020Company ABCAccount 1121860124306
      102/01/2020Company ABCAccount 2175610177575
      103/01/2020Company ABCAccount 3117350118148
      104/01/2020Company ABCAccount 4167070168083
      201/01/2020Company ABCAccount 1113610115604
      202/01/2020Company ABCAccount 2159490161265
      203/01/2020Company ABCAccount 3190330191531
      204/01/2020Company ABCAccount 4214110215431
  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    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'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.