Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Himanshu
Frequent Visitor

Logic for custom buckets

1.JPG

   Hi Team, I am new to power bi, Can you help me to create these bucket as shown in screen shot.  We have Emp ID available in our tables but how to show count of EMP between 2016/2017 buckets?

 

And what is the best visualization we can use to show these types of graphs. ?

 

 

 

 

 

 

 

 

Is I have to use IF condition to create these bucket ..? as ::

 

IF(Data[Amount]<=1000 && YEAR(Data[Date])=(MAX('Date'[Year])-1) || Data[Amount]<=1000 && YEAR(Data[Date])=MAX('Date'[Year]),"<1",
IF(Data[Amount]<=1000 && YEAR(Data[Date])=(MAX('Date'[Year])-1) || Data[Amount]>=1000 && Data[Amount]<2001 && YEAR(Data[Date])=MAX('Date'[Year]),"<1",............................

 

 

 

5 REPLIES 5
v-qiuyu-msft
Community Support
Community Support

Hi @Himanshu,

 

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 to learn matrix visual. 

 

For your requirement, it would be better you can share some sample data of source table, so we can try to test how to model data. 

 

Best Regards,
Qiuyun Yu 

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-qiuyu-msftQiuyun, 

 

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.  

https://www.dropbox.com/s/o6b429w5pagjmkj/Data_Sample.xlsx?dl=0

 

 

 

 

Hi @Himanshu,

 

Can you clarify the logic to return the "<1","1-2" and ">2", and "count of Employee between 2016 and 2017 bucket" regarding your sample data?

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-qiuyu-msft,

 

<1 : Salary less than 1000

1-2 : between 1000-2000

>2 : greater than 2000

 

I have to create 2 bucket 

 

Bucket 2016   |    Bucket 2017

<1                  |     <1

1-2                 |     1-2

>2                  |     >2

 

Logic is :: If in 2016 bucket Emp salary is "<1" and in 2017 bucket Emp salary is ">2"  then the count of Emp comes between highlighted box :

 

22.JPG


Logic is :: If in 2016 bucket Emp salary is "<1" and in 2017 bucket Emp salary is ">2"  then the count of Emp comes between highlighted box : 

 


 

Hi @Himanshu,

 

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

 

q1.PNG

 

Best Regards,
Qiuyun Yu 

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.