percentage below above and below grand total

[Anonymous]

Hello Friends

I am stuck with a problem statement where I need to find percentage in row instead of column just above grand total row (should be month percentage) and below grand total row (total percentage) . Below is the output attached . Please help

	Count1	Count2	Total
March	20	20	40
April	20	23	43
May	30	30	60
June	44	12	56
June Percentage	78.57143	21.42857	100
Total	114	85	199
Total Perentage	57.28643	42.71357	100

[jbwtp]

Hi @Anonymous,

you can calculate the %s in the separate (referring) table and then join it to the orignal one. Something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sUtJRMjKAESYGSrE60UqOBQhhY5CwMVjYN7ESyDE2gBFmENVepXkgNSZAwtAISJiagYVD8ksSc0BihiAZC1MQ09JSKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Count1 = _t, Count2 = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Count1", type number}, {"Count2",  type number}, {"Total",  type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), 
    Build = Table.FromRecords(List.Accumulate(Table.ToRecords(#"Added Index"), {}, (a,n)=> a & {Record.TransformFields(n, {{"Count1", (x) => x / Record.Field(n, "Total")}, {"Count2", (x) => x / Record.Field(n, "Total")}, {"Total", (x) => x / Record.Field(n, "Total")}})}), Value.Type(#"Added Index")),
    #"Added to Column" = Table.TransformColumns(Build, {{"Index", each _ + 0.5, type number}}),
    #"Appended Query" = Table.Combine({#"Added to Column", #"Added Index"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Index", Order.Ascending}})
in
    #"Sorted Rows"

[Anonymous]

Hello

These count columns are already calculated in a custom power bi table (used matrix) and I have many columns like this count1,count2 on different conditions so unable to understand how can i use them in another query, which is not working

[jbwtp]

Hi @Anonymous,

Then this is not clear what do you want to achieve. The code above reproduces the screenshot in your original post. What is your desired output?

Thnaks,

John

[Anonymous]

Hello @jbwtp 

This is something I have achevied within a matrix :

Month	Count1	Count2	Total
March	20	20	40
April	20	23	43
May	30	30	60
June	44	12	56
Total	114	85	199

On top of this I want to achieve this , in same matrix where percentage for month and total needs to be added in below rows.

Month	Count1	Count2	Total
March	20	20	40
April	20	23	43
May	30	30	60
June	44	12	56
June Percentage	78.57143	21.42857	100
Total	114	85	199
Total Perentage	57.28643	42.71357	100

[jbwtp]

Thanks @Anonymous,

Could you please help me to understand, how the screenshot above is different to the output of the code in my earlier message? Do the persantages need to be multiplied by 100?

Thanks,

John

[Anonymous]

Hello John

Thanks for your time on this. 

ASK: How can I use your code if first screenshot (provided by me) is itself an output with the help of DAX query (Matrix Table) to achieve second screenshot output.

Ask2: If I would be able to use the code and can get the output similar to the output you provided in screenshot then how will be able to show values only for  LastMonth and Total.

I hope I am not confusing.

Thanks

Vipin

[jbwtp]

Hi @Anonymous ,

1. Maybe something like this?

Table 2 = Union(Groupby(TOPN(2, 'Table', [Index], DESC), [Month], "Count1", SUMX(CURRENTGROUP(), [Count1]/[Total]*100), "Count2", SUMX(CURRENTGROUP(), [Count2]/[Total]*100), "Total", Sumx(CURRENTGROUP(), 100), "Index",Sumx(CURRENTGROUP(), [Index]+.5)),'Table')

Assuming that the Table is the original table from your post.

2. This can be done as:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sUtJRMjKAESYGSrE60UqOBQhhY5CwMVjYN7ESyDE2gBFmENVepXkgNSZAwtAISJiagYVD8ksSc0BihiAZC1MQ09JSKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Count1 = _t, Count2 = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Count1", type number}, {"Count2",  type number}, {"Total",  type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), 
    Build = Table.FromRecords(List.Accumulate(List.LastN(Table.ToRecords(#"Added Index"),2), {}, (a,n)=> a & {Record.TransformFields(n, {{"Count1", (x) => x / Record.Field(n, "Total")}, {"Count2", (x) => x / Record.Field(n, "Total")}, {"Total", (x) => x / Record.Field(n, "Total")}})}), Value.Type(#"Added Index")),
    #"Replaced Value" = Table.ReplaceValue(Build," ","  ",(x, y, z)=> x & "_",{"Month"}),
    #"Added to Column" = Table.TransformColumns(#"Replaced Value", {{"Index", each _ + 0.5, type number}}),
    #"Appended Query" = Table.Combine({#"Added to Column", #"Added Index"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Index", Order.Ascending}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Month", type text}})
in
    #"Changed Type1"