Forum Discussion
Group 3 columns to visualize one chart
Hello, I have 3 calculations of comparatio for my employees (3 different columns). I would like to create one bin (same ranges for the 3 columns to be able to compare in 1 column chart the number of employees on each bin. Data model is as below and you can see the expected result in the screenshot.
For each employee, I have 3 calculated figures: BS/TDC/TTC
Each 3 should be put into a group (bin) of 10: 90-100,100-110.
The visual I need is the number of employee in the same chart for each group (bin) to be able to compare them.
These calculation and ranges (groups) have been done in Excel manaually so far.
I tried to create another table with my group but I cannot make 3 relationships from 1 table to the other. How do I create only one bin with the same ranges for the 3 different results?
Thank you very much
I have assumed that you want to create bins <60, >150 and in between at intervals of 10. However, you can tweak the below formula for Group BS (replace column names for other columns)
= if [Compa ratio BS]<60 then "<60" else if [Compa ratio BS]>150 then ">150" else Text.From(Number.IntegerDivide([Compa ratio BS],10)*10)&"-"&Text.From(Number.IntegerDivide([Compa ratio BS],10)*10+10)See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TVHJEcQwCOvF7zyCw2EXsFVk0n8biwTZ2Q9DJCwJct/jI+MYvrPIRGt7PEfCE71n0RPdVeiFOem5bNdZuOJDI+vGuFxWuEHAiEDLe5w9HJQq6gVHjRxjoWwtdAGFhsElWoIPBUIb3F4FS79MMhizx/8y10ivKUghRtNgzmiCq86ztEFIE/qDyPrrzDWdhDKAvIzzfd0IftGbSby2lcz7yrL6tojGZ7PvKXUv/jMGtPR4vg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Compa ratio BS" = _t, #"Compa ratio TDC" = _t, #"Compa ratio TTC" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Compa ratio BS", Int64.Type}, {"Compa ratio TDC", Int64.Type}, {"Compa ratio TTC", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Group BS", each if [Compa ratio BS]<60 then "<60" else if [Compa ratio BS]>150 then ">150" else Text.From(Number.IntegerDivide([Compa ratio BS],10)*10)&"-"&Text.From(Number.IntegerDivide([Compa ratio BS],10)*10+10)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Group TDC", each if [Compa ratio TDC]<60 then "<60" else if [Compa ratio TDC]>150 then ">150" else Text.From(Number.IntegerDivide([Compa ratio TDC],10)*10)&"-"&Text.From(Number.IntegerDivide([Compa ratio TDC],10)*10+10)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Group TTC", each if [Compa ratio TTC]<60 then "<60" else if [Compa ratio TTC]>150 then ">150" else Text.From(Number.IntegerDivide([Compa ratio TTC],10)*10)&"-"&Text.From(Number.IntegerDivide([Compa ratio TTC],10)*10+10)) in #"Added Custom2"
1 Reply
I have assumed that you want to create bins <60, >150 and in between at intervals of 10. However, you can tweak the below formula for Group BS (replace column names for other columns)
= if [Compa ratio BS]<60 then "<60" else if [Compa ratio BS]>150 then ">150" else Text.From(Number.IntegerDivide([Compa ratio BS],10)*10)&"-"&Text.From(Number.IntegerDivide([Compa ratio BS],10)*10+10)See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TVHJEcQwCOvF7zyCw2EXsFVk0n8biwTZ2Q9DJCwJct/jI+MYvrPIRGt7PEfCE71n0RPdVeiFOem5bNdZuOJDI+vGuFxWuEHAiEDLe5w9HJQq6gVHjRxjoWwtdAGFhsElWoIPBUIb3F4FS79MMhizx/8y10ivKUghRtNgzmiCq86ztEFIE/qDyPrrzDWdhDKAvIzzfd0IftGbSby2lcz7yrL6tojGZ7PvKXUv/jMGtPR4vg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Compa ratio BS" = _t, #"Compa ratio TDC" = _t, #"Compa ratio TTC" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Compa ratio BS", Int64.Type}, {"Compa ratio TDC", Int64.Type}, {"Compa ratio TTC", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Group BS", each if [Compa ratio BS]<60 then "<60" else if [Compa ratio BS]>150 then ">150" else Text.From(Number.IntegerDivide([Compa ratio BS],10)*10)&"-"&Text.From(Number.IntegerDivide([Compa ratio BS],10)*10+10)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Group TDC", each if [Compa ratio TDC]<60 then "<60" else if [Compa ratio TDC]>150 then ">150" else Text.From(Number.IntegerDivide([Compa ratio TDC],10)*10)&"-"&Text.From(Number.IntegerDivide([Compa ratio TDC],10)*10+10)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Group TTC", each if [Compa ratio TTC]<60 then "<60" else if [Compa ratio TTC]>150 then ">150" else Text.From(Number.IntegerDivide([Compa ratio TTC],10)*10)&"-"&Text.From(Number.IntegerDivide([Compa ratio TTC],10)*10+10)) in #"Added Custom2"