I am trying to allocate the total value of a channel to others based on Gross Revenue in Power Query. The data source is from SQL, a query that i made to recover the data from the channel and gross revenue.
In this case I am trying to allocate the total value of channel C (Total of C = 4) to A and B, based on its Gross Revenue.
This a dummy table, simplified from the thousands of rows and several columns that I have:
| Channel | GR |
|---|---|
| A | 5 |
| A | 1 |
| B | 10 |
| B | 4 |
| C | 1 |
| C | 3 |
The table from PowerQuery that I expect is as following:
| Channel | GR | GR2 |
|---|---|---|
| A | 5 | 6 |
| A | 1 | 1.2 |
| B | 10 | 12 |
| B | 4 | 4.8 |
| C | 1 | 0 |
| C | 3 | 0 |
I can do it in Excel, as it follows – however, as I said before, I cannot use it because the data source comes from SQL and want to present several graphs and information about this table in PowerBI [Formula used in Excel, if it helps some way =IF(G2="C";0;H2+SUMIFS(H:H;G:G;"C")*(H2/(SUM($H$2:$H$18)-SUMIFS(H:H;G:G;"C"))))] :
Solving the problem using Excel
I do not understand M languange enough to replicate this formula and everything I tried it just results in Erros in each line.
Thanks in advance for your help.
TL;DR: Trying to allocate the total of the channel value to the other channel based on GR
>Solution :
Try this in powerquery
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Channel", type text}, {"GR", Int64.Type}}),
#"NotCFiltered Rows" = Table.SelectRows(#"Changed Type", each ([Channel] <> "C")),
#"CFiltered Rows" = Table.SelectRows(#"Changed Type", each ([Channel] = "C")),
#"Added Custom" = = Table.AddColumn(#"NotCFiltered Rows", "GR2", each ( [GR]/ List.Sum(#"NotCFiltered Rows"[GR])) * List.Sum(#"CFiltered Rows"[GR]) + [GR] ,type number)
in #"Added Custom"
99% certain this wont work for you because the final answer is going to depend on other columns you omitted that need to group, but this was your question
