Percentage calculation in line chart

[danielsun]

Hi all,

in my power bi, I need to design a line chart
here is a table: market_mbs_avg_upb_disc
columns:
agency: GSE, GN
agency_grp: different hierarchical sub-agency, like FN, FG and so fourth
amorttype_grp: FRM, ARM
term_grp: 30Y, 15Y
upb: balance
loan_count: loan count
cut: Channel, Loan Purpose, Occupancy, Property Type, Number of Borrowers, State
cut_grp: different hierarchical sub-cut
fctrdt: factor date

Now I want to create 6 different cuts line chart by "cut"
There are some outer slicers: agency, agency_grp, amorttype_grp, term_grp, fctrdt and cut_grp(this is used for selecting some certain cut_grp to show)
In the line chart, I want to use stacked area chart to show percentage for each cut_grp's balance or loan_count / each cut's balance or loan_count.
As you know, I have create a table:
selector_mbs_avg_upb =
DATATABLE(
"Name", STRING,
"Order", INTEGER,
{
{"UPB", 1},
{"Loan Count", 2}
}
)

[Name] is also to be an outer slicer to choose how to calculate
cut_grp will be set as Legend in this line chart.

Value =
VAR numerator =
SWITCH(
SELECTEDVALUE(selector_mbs_avg_upb[Name]),
"UPB", SUM(market_mbs_avg_upb_disc[upb]),
"Loan Count", SUM(market_mbs_avg_upb_disc[loan_count])
)
VAR denominator =
CALCULATE(
numerator,
ALLSELECTED(market_mbs_avg_upb_disc[cut_grp])
)
RETURN DIVIDE(numerator, denominator, 0)

here is my refined DAX.
I have to say market_mbs_avg_upb_disc is made a relationship with market_mbs_avg_upb_disc by cut_grp
the cut_grp slicer is taken from the dimension table.

By the way, I have set cut = "Channel" in the filter panel.

This refined DAX, only show all 100%. what happened?

[Elena_Kalina]

Hi @danielsun 

Based on your data, I tried to reproduce your situation. Below are my data in tables, relationships between them and the measures which I used

GroupValue = 
SWITCH(
    SELECTEDVALUE(selector_mbs_avg_upb[Name], "UPB"),
    "UPB", SUM(market_mbs_avg_upb_disc[upb]),
    "Loan Count", SUM(market_mbs_avg_upb_disc[loan_count])
)

DateTotalValue = 
VAR Metric = SELECTEDVALUE(selector_mbs_avg_upb[Name], "UPB")
RETURN
CALCULATE(
    SWITCH(Metric,
        "UPB", SUM(market_mbs_avg_upb_disc[upb]),
        "Loan Count", SUM(market_mbs_avg_upb_disc[loan_count])
    ),
    REMOVEFILTERS(market_mbs_avg_upb_disc[cut_grp]),  // Ignore group filter
    market_mbs_avg_upb_disc[cut] = "Channel",        // Hard-coded filter
    KEEPFILTERS(                                     // Preserve date context
        market_mbs_avg_upb_disc[fctrdt] IN VALUES(market_mbs_avg_upb_disc[fctrdt])
    )
)

DynamicPercentage = 
VAR Numerator = [GroupValue]
VAR Denominator = [DateTotalValue]
RETURN
    IF(
        ISBLANK(Numerator) || Denominator = 0, 
        BLANK(), 
        DIVIDE(Numerator, Denominator)
    )

[DataNinja777]

Hi @danielsun ,

The issue you're encountering, where every line in your chart displays 100%, is a common scenario in DAX related to how filter contexts are handled. The root of the problem lies in your use of the ALLSELECTED function within your denominator calculation. The ALLSELECTED function is designed to respect filters that come from outside the current visual, such as slicers. When you use your cut_grp slicer, ALLSELECTED calculates a total based only on the items you have selected in that slicer. If you select only one cut_grp to view, the numerator (the value for that single group) becomes identical to the denominator (the total of all selected groups, which is just that one group), resulting in a division that always equals 1, or 100%.

To achieve the correct percentage calculation, you need the denominator to represent the total for the parent cut (e.g., "Channel"), completely ignoring any selections made in the cut_grp slicer or the filtering applied by the chart's legend. The appropriate DAX function for this purpose is ALL. The ALL function removes all filters from a given column, providing a stable total for your percentage calculation, regardless of the current selection.

Here is the refined DAX measure that replaces ALLSELECTED with ALL to correctly calculate the percentage value.

Percentage Value =
VAR _Numerator =
    SWITCH(
        SELECTEDVALUE(selector_mbs_avg_upb[Name]),
        "UPB", SUM(market_mbs_avg_upb_disc[upb]),
        "Loan Count", SUM(market_mbs_avg_upb_disc[loan_count])
    )
VAR _Denominator =
    CALCULATE(
        _Numerator,
        ALL(market_mbs_avg_upb_disc[cut_grp])
    )
RETURN
    DIVIDE(_Numerator, _Denominator)

Since you mentioned you are using a separate dimension table for your cut_grp slicer, which is an excellent practice, the formula should be made more robust by referencing the column from that dimension table. Assuming your dimension table is named DimCutGrp, the best-practice version of the measure would look like this.

Percentage Value (Best Practice) =
VAR _Numerator =
    SWITCH(
        SELECTEDVALUE(selector_mbs_avg_upb[Name]),
        "UPB", SUM(market_mbs_avg_upb_disc[upb]),
        "Loan Count", SUM(market_mbs_avg_upb_disc[loan_count])
    )
VAR _Denominator =
    CALCULATE(
        _Numerator,
        ALL('DimCutGrp'[cut_grp]) 
    )
RETURN
    DIVIDE(_Numerator, _Denominator)

This corrected formula works because the _Denominator variable is now calculated in a modified filter context. The CALCULATE function, combined with ALL('DimCutGrp'[cut_grp]), computes the total sum while completely ignoring any filters on the cut_grp column. This ensures the denominator is always the grand total for the parent cut being analyzed, while still respecting all other active slicers like agency, term_grp, and fctrdt. The _Numerator continues to be calculated in the original context of the chart, representing the value for each individual cut_grp, which allows the DIVIDE function to return a true percentage of the total.

To implement this solution, you would first create a new measure in Power BI using the corrected DAX code. After creating it, select the measure and use the "Measure tools" ribbon to format it as a percentage. Then, configure your stacked area chart by placing the fctrdt column on the X-axis, your new [Percentage Value] measure on the Y-axis, and the market_mbs_avg_upb_disc[cut_grp] column in the Legend field. Finally, select the chart visual and use the Filters pane to apply a filter on the cut column, setting it to "Channel" or any other parent category you wish to display. This will result in a chart that correctly visualizes the percentage contribution of each sub-group over time.

Best regards,