calculate revenue per "dynamic" category based on period slicer selection

[bsas]

Hi All,

I'm struggling with approach to calculate revenue per "dynamic" category which is based on period slicer selection. In case month granularity appears on chart show revenue per category monthly, if quarter - show revenue (sum of all revenue in quarter) per category, if year - show revenue (sum of all revenue in year) per category.
Have fact table with monthly granularity and relation to date table on date filelds.
Is it even possible to calculate such logic in Power BI?
Sample of data

Account	Date	Amount
1111	1/1/2022	$       100
1111	2/1/2022	$       300
1111	3/1/2022	$       200
1111	4/1/2022	$       100
1111	5/1/2022	$       600
1111	6/1/2022	$       200
1111	7/1/2022	$       300
2222	1/1/2022	$       600
2222	2/1/2022	$       400
2222	3/1/2022	$       500
2222	4/1/2022	$       400
2222	5/1/2022	$       500
2222	6/1/2022	$       600
2222	7/1/2022	$       700
2222	8/1/2022	$       600

categoty values

small	mid	large
<= 300	> 300 <= 1000	> 1000

Expected result
monthly aggregation

quarter aggregation

in case of year aggregation

[amitchandak · Accepted solution]

@bsas , You need to consider Dynamic Segmentation, but buckets will static

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[v-rzhou-msft · Accepted solution]

Hi @bsas ,

I think you want to show two legends [Category] and [Amount] in Stacked column chart in Power BI. Currently, Power BI doesn't support us to add two legends directly. Here I suggest you to try Small multiples function.

Data model:

Measure:

Amount by Category =
SWITCH (
    SELECTEDVALUE ( Category[Category] ),
    "Small",
        IF (
            CALCULATE ( SUM ( 'Table'[ Amount ] ) ) <= 300,
            CALCULATE ( SUM ( 'Table'[ Amount ] ) )
        ),
    "Mid",
        IF (
            CALCULATE ( SUM ( 'Table'[ Amount ] ) ) <= 1000
                && CALCULATE ( SUM ( 'Table'[ Amount ] ) ) > 300,
            CALCULATE ( SUM ( 'Table'[ Amount ] ) )
        ),
    "Large",
        IF (
            CALCULATE ( SUM ( 'Table'[ Amount ] ) ) > 1000,
            CALCULATE ( SUM ( 'Table'[ Amount ] ) )
        )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@bsas , You need to consider Dynamic Segmentation, but buckets will static

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[bsas]

Thank you @amitchandak ! I was moving in similar direction but your article inspired on new idea. At the end for my case I created separate table with ID and Label fields then created measure using switch and sumx. 

____RevenueCustomerSizeChart =

SWITCH(

    SELECTEDVALUE(CustomerSize[SizeID]),

    1, SUMX( FILTER( VALUES( Data[AccountID]),

                [____RevenueComposite] >= 20000000), [____RevenueComposite]),

    2, SUMX( FILTER( VALUES( Data[AccountID]),

                [____RevenueComposite] >= 10000000 && [____RevenueComposite] < 20000000), [____RevenueComposite]),

    3, SUMX( FILTER( VALUES( Data[AccountID]),

                [____RevenueComposite] >= 5000000 && [____RevenueComposite] < 10000000), [____RevenueComposite]),

    4, SUMX( FILTER( VALUES( Data[AccountID]),

                [____RevenueComposite] >= 1000000 && [____RevenueComposite] < 5000000), [____RevenueComposite]),

    5, SUMX( FILTER( VALUES( Data[AccountID]),

                [____RevenueComposite] >= 500000 && [____RevenueComposite] < 1000000), [____RevenueComposite]),

    6, SUMX( FILTER( VALUES( Data[AccountID]),

                [____RevenueComposite] > 0 && [____RevenueComposite] < 500000), [____RevenueComposite])

)