Forum Discussion

riteshmen's avatar
riteshmen
Regular Visitor
9 months ago
Solved

Stacked bar chart with three customer group types: Internal, Prior Year Loss, and New Year Loss.

I want to implement a stacked bar chart that categorizes customers into three groups:

Internal Customers
– Identified using a calculated column in the fact table.
– If the calculated column indicates “Internal”, they fall under this category; otherwise, the field is blank.

Prior Year Loss-Making Customers

New Year Loss-Making Customers

The chart should display a 13-month rolling timeline based on the selected MonthYY slicer.
For example, if I select Mar 2025, the visual must show data from Mar 2024 to Mar 2025.

For each bar in the timeline, I want to show YTD negative GP customers:

Fiscal year starts in April

Each bar must represent YTD performance from April 1st to that respective month

Only include customer groups where cumulative YTD GP is negative

I am currently able to calculate the correct total number of customers across 13 months.
However, I am facing challenges in splitting the results into the three categories:

-Internal customers

-Prior year loss-making customers

-New year loss-making customers

Below is the DAX measure I am using (but it does not correctly break the results into the three categories. 

 

Count CustomerGroup FY YTD Neg 1911 =
VAR ThisMonth = MAX('Calendar'[Date])  --MAX('CalendarPresentation'[Date]) --  
-- SELECTEDVALUE('Calendar'[Date])
RETURN
IF (
    NOT ISBLANK(ThisMonth),
    COUNTROWS (
        FILTER (
            VALUES('GL+GF'[Customer Group]),
            CALCULATE([GP FY YTD 1911]) < 0
        )
    )
)
 
----
GP FY YTD 1911 =
VAR AxisMonth =  
-- MAX('Calendar'[Date])
MAX('CalendarPresentation'[Date])

VAR FYStart =
    DATE(
        YEAR(AxisMonth) - IF(MONTH(AxisMonth) < 4, 1, 0),
        4,
        1
    )

RETURN
CALCULATE(
    SUM('GL+GF'[GP]),
    DATESBETWEEN(
        'Calendar'[Date],
        FYStart,
        AxisMonth
    )
)

 

 
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    9 months ago

    @riteshmen ,  -Prior year loss-making customers  and -New year loss-making customers seems to measures not column values. In such case we have two option, we create measure and create calculation group and use that. Or we do Dynamic segmentation 


    example 
    Learn Power BI Advance - Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

    :Calculation Groups (Can now be created in Desktop)- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0 

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    9 months ago

    riteshmen ,  -Prior year loss-making customers  and -New year loss-making customers seems to measures not column values. In such case we have two option, we create measure and create calculation group and use that. Or we do Dynamic segmentation 


    example 
    Learn Power BI Advance - Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

    :Calculation Groups (Can now be created in Desktop)- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0 

  • Praful_Potphode's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    9 months ago

    Hi riteshmen ,

     

    try below column :

    CustomerGroupNew = SWITCH(TRUE(),
'GL+GF'[Customer Group]<>"internal" && 'GL+GF'[GP]<0 && YEAR('GL+GF'[MonthYY])=YEAR(TODAY())-1,"Prior Year Loss-Making Customer",
'GL+GF'[Customer Group]<>"internal" && 'GL+GF'[GP]<0 && YEAR('GL+GF'[MonthYY])=YEAR(TODAY()),"New Year Loss-Making Customer",
'GL+GF'[Customer Group]
)

    Then use this column for stacked column chart as legend.in legend,exclude blank(blank represents customer whose GP is positive).

    Sample PBIX

    Please give kudos or mark it as solution once confirmed.

     

    Thanks and regards,

    Praful

     

     

     

     

     

    • riteshmen's avatar
      riteshmen
      Regular Visitor
      9 months ago

      This is a calculated column which is not providing the correct output. I want to show the customer group count and differentiate it as internal,prior and new based on the monthyy selection. so it should be dynamic.

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        9 months ago

        @riteshmen ,  -Prior year loss-making customers  and -New year loss-making customers seems to measures not column values. In such case we have two option, we create measure and create calculation group and use that. Or we do Dynamic segmentation 


        example 
        Learn Power BI Advance - Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

        :Calculation Groups (Can now be created in Desktop)- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    9 months ago

    Hi,

    Quite confused about what youw want.  Of the 3 groups, 2 are time based (PY and current year) but one is not!!

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    9 months ago

    Hi riteshmen ,
    Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.