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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors