DAX percent of total by category

Question

I have two identical table visuals and a measure calculating percent for category.

% = 
DIVIDE(
    [m_SalesValue]
    ,CALCULATE([m_SalesValue], ALLSELECTED('Table'[Category]))
    ,BLANK()
)

When I select (highlight) one category, say banana, then the other visual shows unexpected percent value for category. The expected value is 0.25 but it shows 1.00. How should I modify the measure to get the expected results.

Getting the challenge to higher level, let's assume that both visuals have been initially cross filtered by some unspecified dimension, say fruits. That is why I used ALLSELECTED instead of ALL.

Here is source table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lEyVYrViVZKSswDQiDXAsxNzkgtKqoEcs3B3PLEktSi3NSc/DygkKGRUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, SalesValue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"SalesValue", Int64.Type}})
in
    #"Changed Type"

Ryan B. Ryan B. · Accepted Answer · 2019-08-20T14:33:30

You'll want to use ALLEXCEPT and identify the cross-filters that you still want to apply. Your example suggests 'Fruits' so that's what I included below.

% = 
DIVIDE(
    [m_SalesValue]
    ,CALCULATE([m_SalesValue], ALLEXCEPT(Table, 'Table'[Fruits]))
    ,BLANK()
)

DAX percent of total by category

1 Answers