Power BI DAX - Percent of Total Column with FILTERs

Question

I'm relatively new to Power BI - have looked high and low for help with "percent of total" logic in a measure and have found only examples where filters ARE NOT applied and I need to apply filters in the DAX code.

I have come up with the following, which gives me all ones (100% values instead of the percentages of the total). I think I need to do something different with the ALLSELECTED, etc., but could use any thoughts that any of you have. BTW, I have tried every combination of SUM, SUMX, ALL, ALLEXCEPT, ALLSELECTED, etc. Many thanks in advance for your guidance.

Mix % =
CALCULATE (
    SUM ( service_line_analysis_unpivot[Value] ),
    service_line_analysis_unpivot[Attribute] = "netrevenue"
)
    / CALCULATE (
        SUMX ( service_line_analysis_unpivot, service_line_analysis_unpivot[Value] ),
        FILTER (
            service_line_analysis_unpivot,
            service_line_analysis_unpivot[Attribute] = "netrevenue"
        ),
        ALLSELECTED ()
    )

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-18T23:01:21

If you want net revenue divided by total net revenue for everything in your slicer/filter selection, then I think this is what you want:

Mix % =
DIVIDE (
    CALCULATE (
        SUM ( service_line_analysis_unpivot[Value] ),
        service_line_analysis_unpivot[Attribute] = "netrevenue"
    ),
    CALCULATE (
        SUM ( service_line_analysis_unpivot[Value] ),
        service_line_analysis_unpivot[Attribute] = "netrevenue",
        ALLSELECTED ()
    )
)

If that's not right, then we'll need to see more detail on your data table, filters, and visuals.

Power BI DAX - Percent of Total Column with FILTERs

1 Answers