Logic for custom buckets

Question

&nbsp; &nbsp;Hi Team, I am new to power bi, Can you help me to create these bucket as shown in screen shot. &nbsp;We have Emp ID available in our tables but how to show count of EMP between 2016/2017 buckets?&nbsp;And what is the&nbsp;best visualization we can use to show these types of graphs. ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Is I have to use IF condition to create these bucket ..? as ::&nbsp;IF(Data[Amount]&lt;=1000 &amp;&amp; YEAR(Data[Date])=(MAX('Date'[Year])-1) || Data[Amount]&lt;=1000 &amp;&amp; YEAR(Data[Date])=MAX('Date'[Year]),"&lt;1",IF(Data[Amount]&lt;=1000 &amp;&amp; YEAR(Data[Date])=(MAX('Date'[Year])-1) || Data[Amount]&gt;=1000 &amp;&amp; Data[Amount]&lt;2001 &amp;&amp; YEAR(Data[Date])=MAX('Date'[Year]),"&lt;1",............................&nbsp;&nbsp;&nbsp;

v-qiuyu-msft · Answer

Logic is :: If in 2016 bucket Emp salary is "&lt;1" and in 2017 bucket Emp salary is "&gt;2" &nbsp;then the count of Emp comes between highlighted box :&nbsp;
&nbsp;

&nbsp;
Hi&nbsp;Himanshu,
&nbsp;
You can go to Query Editor, filter table for 2016 and 2017 year separately&nbsp;then merge them based on Emp. Then create calculated columns in report to return &lt;1,1-2,&gt;2, use matrix to visualize data. &nbsp;For more information, please see attached pbix file.&nbsp;
&nbsp;

&nbsp;
Best Regards,Qiuyun Yu&nbsp;
&nbsp;
&nbsp;

v-qiuyu-msft · Answer

Hi&nbsp;Himanshu,
&nbsp;
For the screenshot, you need to use Matrix visual to visualize data, Bucket 2017 in Row group, Bucket 2016 in column group and the count values as matrix values. You can go through this tutorial&nbsp;to learn matrix visual.&nbsp;
&nbsp;
For your requirement, it would be better&nbsp;you can share some sample data of source table, so we can try to test how to model&nbsp;data.&nbsp;
&nbsp;
Best Regards,Qiuyun Yu&nbsp;
&nbsp;
&nbsp;

himanshu · Answer

Hi v-qiuyu-msftQiuyun,&nbsp;&nbsp;Thanks for reply.. I have attached an excel file.. We have to craete 2 bucket (for 2016/2017) and show the count of Emp between them. &nbsp;https://www.dropbox.com/s/o6b429w5pagjmkj/Data_Sample.xlsx?dl=0&nbsp;&nbsp;&nbsp;&nbsp;

v-qiuyu-msft · Answer

Hi&nbsp;Himanshu,
&nbsp;
Can you clarify the logic to return the "&lt;1","1-2" and "&gt;2", and "count of Employee between 2016 and 2017 bucket" regarding your sample data?
&nbsp;
Best Regards,Qiuyun Yu&nbsp;

himanshu · Answer

Hi&nbsp;v-qiuyu-msft,&nbsp;&lt;1 : Salary less than 10001-2 : between 1000-2000&gt;2 : greater than 2000&nbsp;I have to create 2 bucket&nbsp;&nbsp;Bucket 2016 &nbsp; | &nbsp; &nbsp;Bucket 2017&lt;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &lt;11-2&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 1-2&gt;2&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &gt;2&nbsp;Logic is :: If in 2016 bucket Emp salary is "&lt;1" and in 2017 bucket Emp salary is "&gt;2" &nbsp;then the count of Emp comes between highlighted box :&nbsp;

Forum Discussion

Logic for custom buckets

5 Replies

Related content

Logic problem?

FX Conversion Logic

Custom Tooltip Not Passing Single-Country Filter Context When Y Axis Uses Conditional Logic

Slicer by logic conditions

Managing logical data unbundling between companies