Percent of Total Windowed in Power BI DAX

user_1179880 0
Viewed 3372

I have these two tables:

A data table...

ImaginaryData = 
DATATABLE (
    "Fruit", STRING,
    "Colour", STRING, 
    "Amount", INTEGER, 
    { 
        { "Apple", "Red", 10 }, 
        { "Apple", "Green", 5 },
        { "Pear", "Pink", 100 },
        { "Pear", "Blue", 65 },
        { "Orange", "Black", 12 },
        { "Orange", "White", 8 }
    } )

A lookup table...

ImaginaryLookup = 
DATATABLE (
    "Fruit", STRING, 
    { 
        { "Apple" }, 
        { "Pear" },
        { "Orange" }
    } )

Then I created this simple 1-to-many relationship:

enter image description here

Now I am trying to create a Percent of Total measure to add to a table - but I want the percent of total to be based on the total of the fruits that are selected in a slicer of ImaginaryLookup:

This is what I have tried:

 %Total = 
DIVIDE(
    sum( ImaginaryData[Amount] ),
    CALCULATE(
        sum( ImaginaryData[Amount] ),
        ALL(ImaginaryData)
    )
)

If all fruits are selected in the slicer then it works fine as I'd expect:

enter image description here

But if I select say "Orange" then the results are not what I want, as I'd like the 12 and the 8 to be a percentage of 20:

enter image description here

This also does not give me what I want:

%Total = 
DIVIDE(
    sum( ImaginaryData[Amount] ),
    CALCULATE(
        sum( ImaginaryData[Amount] ),
        ALLEXCEPT(ImaginaryData, ImaginaryData[Fruit])
    )
)

Because now if I select say Pears and Oranges then it is giving me the percent as a percent of each fruits total, rather than the percent of 185:

enter image description here

note to Alexis

If I try this:

%Total = 
DIVIDE(
    sum( ImaginaryData[Amount] ),
    CALCULATE(
        sum( ImaginaryData[Amount] ),
        ALLSELECTED( ImaginaryData[Fruit])
    )
)

I get this:

enter image description here

dax powerbi
2 Answers

The ALL function removes all filter context. Try it with ALLSELECTED instead. That will preserve your slicer selection while removing the table visual's filter context.

If you use this (note I didn't specify a column):

%Total = 
    DIVIDE(
        SUM( ImaginaryData[Amount] ),
        CALCULATE(
            SUM( ImaginaryData[Amount] ),
            ALLSELECTED( ImaginaryData )
        )
    )

Then you should get this result:

% Total Measure

The reason it doesn't work if you do ALLSELECTED(ImaginaryData[Fruit]) is that the Colour filter context still exists, so you don't pick up the other fruits because those are all different colors than the row you are evaluating on.

user_7652260

I used following measure and it works as expected:

%Total = 
CALCULATE(DIVIDE(sum(ImaginaryData[Amount]),
CALCULATE(SUM(ImaginaryData[Amount]),ALLSELECTED(ImaginaryData))))
user_27141750
Related