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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bsas
Post Patron
Post Patron

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

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

AccountDate Amount 
11111/1/2022 $       100
11112/1/2022 $       300
11113/1/2022 $       200
11114/1/2022 $       100
11115/1/2022 $       600
11116/1/2022 $       200
11117/1/2022 $       300
22221/1/2022 $       600
22222/1/2022 $       400
22223/1/2022 $       500
22224/1/2022 $       400
22225/1/2022 $       500
22226/1/2022 $       600
22227/1/2022 $       700
22228/1/2022 $       600

categoty values

smallmidlarge
<= 300> 300 <= 1000> 1000


Expected result
monthly aggregation

bsas_0-1675358823925.png
quarter aggregation

bsas_1-1675358856865.png

in case of year aggregation

bsas_2-1675358933659.png

 




2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

View solution in original post

v-rzhou-msft
Community Support
Community Support

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:

RicoZhou_0-1675409752029.png

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.

RicoZhou_1-1675409768098.png

RicoZhou_2-1675409778620.png

RicoZhou_3-1675409786722.png

 

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.

 

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

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:

RicoZhou_0-1675409752029.png

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.

RicoZhou_1-1675409768098.png

RicoZhou_2-1675409778620.png

RicoZhou_3-1675409786722.png

 

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
Super User
Super User

@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

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])
)

 



Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors