How to ignore slicer in Percentage calculation

[yakovlol]

Hello PowerBi community,

Could you please help me with a measure, it seems like simple one but cannot figure out how to do

I have a measure for percantage calculation.

It calculate percantage based on each Type, in simple words percantage by row (screen below)

Measure2 = 
DIVIDE(
    SUM(Hours[Hours])
    ,CALCULATE(
        SUM(Hours[Hours])
        ,ALLSELECTED(Hours[Type ])
    )
)

But I also have a slcier on my page, and i want to select 1 type for example "Peaches". So that i want to check percentage only in theese type.

The percentage, however, gets recalculated when I select, and I now have 100% for every row in this type

How can I adjust my percantge measure so that when I use my slicer to filter, the percantage does not recalculate?

My pbix.file

Many thanks for your help!

[Greg_Deckler]

@yakovlol Try:

Measure2 = 
DIVIDE(
    SUM(Hours[Hours])
    ,CALCULATE(
        SUM(Hours[Hours])
        ,ALL(Hours[Type ])
    )
)

or

Measure2 = 
  VAR __Sum = SUM('Hours'[Hours])
  VAR __SumAll = SUMX(ALL('Hours'), [Hours])
  VAR __Result = DIVIDE( __Sum, __SumAll )
RETURN
  __Result

[yakovlol]

hello @Greg_Deckler thanks for your answer. It works on my test model, but I cannot reproduce it on my real Table with calculation.
I tried this measure 

Measure2 = DIVIDE( SUM(Hours[Hours]) ,CALCULATE( SUM(Hours[Hours]) ,ALL(Hours[Type ]) ) )

So I have the correct % distribution on the first level (yellow one) in my hierarchy but then I always have 100% for the each next level (blue one), and i don't know why.

Maybe you know what could be the reason for that, and why measure is not working on the next levels?

[Greg_Deckler]

@yakovlol You could try:

Measure2 = DIVIDE( SUM(Hours[Hours]) ,CALCULATE( SUM(Hours[Hours]) ,ALL('Hours') ) )

Not sure what columns you have in your Rows for your Matrix.

[yakovlol]

@Greg_Deckler thanks for the new approach, but I still have an issue

I tried that measure but seems the % is now calculated from absolutely all data from my table. And I have too small a percatage, but I want to have 100% for each rows in my Matrix

Cannot figure out what is wrong here.

Maybe you have any other suggestions how to deal with that?

Thank you

[Greg_Deckler · Accepted solution]

@yakovlol These kinds of context issues are hard to troubleshoot without sample data or some kind of sample model. Let's say that your rows in your matrix are based on Column1 and Column2 for arguments sake. Your columns are based on Column3. The percentage value is Column4.

Based on this, you could create a measure like the following:

Measure = 
    VAR __Column1 = MAX( 'Table'[Column1] )
    VAR __Column2 = MAX( 'Table'[Column2] )
    VAR __Value = SUM( 'Table'[Column4])
    VAR __Column1Total = SUMX(FILTER(ALL('Table'), [Column1] = __Column1), [Column4])
    VAR __Column2Total = SUMX(FILTER(ALL('Table'), [Column1] = __Column1 && [Column2] = __Column2), [Column4])
    VAR __Result = IF(ISINSCOPE('Table'[Column2]), DIVIDE(__Value, __Column2Total), DIVIDE(__Value, __Column1Total))
RETURN
    __Result

PBIX is attached below signature.