create slabs or buckets with respect to measure (Volume)

[Rajendrabobade]

Hi Team,

I am stuck in requirement your help appreciated.

I want to create slabs or buckets with respect to measure (Volume) as below

1. volume is <100
2. Volume >=100 and Volume<=1000
3. Volume >1000

And against these bucket I want to show various calculation like Current year Units, Current year Volume, Prev year Units, prev year Volume etc in a table(Matrix) as below.

Slab	CY Unit	CY Volume	PY Unit	PY Volume
<100
100-1000
>1000

I have tried using creating dimension but it calculate slabs (bucket) in row wise. But I want to calculate the buckets dynamically.

Hope I have explain my requirement correctly.  

-------------------------------------------------------------------------

Please find below requirmnt with example 

Example of my requirment

suppose i have below data with month, year, grscode in filter .

Table
Year     month   Grs code   Volume
2020    Jan       GRS100       55
2020    Feb      GRS100       102
2020   March   GRS200       1001

Now when i select Month Filter - Jan then my value sholud be 55 .
Now slab is below <100 for jan month .
Like :  slab     volume
        <100      55
this right for single selection .

but when try to select multiple month like - Jan & feb then volume should be 55+102 and slab category is 100- 1000 .
this time slab is automaticaly change and shwoing in to slab on 100-1000

Like  :slab     Volume
     100- 1000   157

I need grs code wise monthly dynamic volume in slabs.

when we select Month - jan  and grscode = GRS100 then below oiuput requiired. 

Slab       volume 

<100   55

when we select Month - jan & Feb  and grscode = GRS100 then below ouput is requiired. 

Slab       volume 

100-1000   157

Hope I have explain my requirement correctly.  

[Fowmy · Accepted solution]

@Rajendrabobade 

Hope you need the following output

You can download the file: HERE

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[v-diye-msft]

Hi @Rajendrabobade 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[Fowmy · Accepted solution]

@Rajendrabobade 

Hope you need the following output

You can download the file: HERE

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Rajendrabobade]

Hi @Fowmy ,

As per shared screenshot i need same ouput .

can you send me text calculation (calculated formula)you crated from your end, becoase i am not abel to download yoor shared file .

[Fowmy · Accepted solution]

@Rajendrabobade 

Please check this link : https://1drv.ms/u/s!AmoScH5srsIYgYQLBoiJv0WnldAHRQ?e=7AfoAb

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Fowmy]

@Rajendrabobade 

Not quite clear about your requirement but I feel you need something like dynamic grouping. Refer this video:

https://www.youtube.com/watch?v=jLloQ_1KiGI

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Rajendrabobade]

Hi @Fowmy 

Please find below requirmnt with example 

Example of my requirment

suppose i have below data with month, year, grscode in filter .

Table
Year     month   Grs code   Volume
2020    Jan       GRS100       55
2020    Feb      GRS100       102
2020   March   GRS200       1001

Now when i select Month Filter - Jan then my value sholud be 55 .
Now slab is below <100 for jan month .
Like :  slab     volume
        <100      55
this right for single selection .

but when try to select multiple month like - Jan & feb then volume should be 55+102 and slab category is 100- 1000 .
this time slab is automaticaly change and shwoing in to slab on 100-1000

Like  :slab     Volume
     100- 1000   157

I need grs code wise monthly dynamic volume in slabs.

when we select Month - jan  and grscode = GRS100 then below oiuput requiired. 

Slab       volume 

<100   55

when we select Month - jan & Feb  and grscode = GRS100 then below ouput is requiired. 

Slab       volume 

100-1000   157

Hope I have explain my requirement correctly. 

[FarhanAhmed]

I think it is best to create 3 measure and show them as columns/rows in your table

[Rajendrabobade]

hi @FarhanAhmed 

i agree but we need 3 slabs in one matrix only .

Please find below requirmnt with example 

Example of my requirment

suppose i have below data with month, year, grscode in filter .

Table
Year     month   Grs code   Volume
2020    Jan       GRS100       55
2020    Feb      GRS100       102
2020   March   GRS200       1001

Now when i select Month Filter - Jan then my value sholud be 55 .
Now slab is below <100 for jan month .
Like :  slab     volume
        <100      55
this right for single selection .

but when try to select multiple month like - Jan & feb then volume should be 55+102 and slab category is 100- 1000 .
this time slab is automaticaly change and shwoing in to slab on 100-1000

Like  :slab     Volume
     100- 1000   157

I need grs code wise monthly dynamic volume in slabs.

when we select Month - jan  and grscode = GRS100 then below oiuput requiired. 

Slab       volume 

<100   55

when we select Month - jan & Feb  and grscode = GRS100 then below ouput is requiired. 

Slab       volume 

100-1000   157

Hope I have explain my requirement correctly. 

[FarhanAhmed]

You need to create 4 measure like this.

once you done it put those in Values of Matrix Visual and Fields in Columns to get desired results

_Volume = SUM(Table[Volume])
<100 = IF (_Volume <100,_Volume,BLANK())
100-1000 = IF (_Volume >=100 && _Volume <=1000,_Volume,BLANK())
>1000 = IF (_Volume >1000 ,_Volume,BLANK())

[amitchandak]

@Rajendrabobade , refer SEGMENTATION

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bi...
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-i...