ALL and ALLSELECTED in the same measure

[pr92]

I have the following data in my table : 

Segment	Category	Sales	Country
Old	O	5200	FR
Old		3200	FR
New	O	5900	FR
New		1200	FR
Recent	O	3500	FR
Recent		5400	FR
New	O	4200	IT
New		2500	IT
Recent	O	1200	IT
Recent		3500	IT
Old	O	9500	IT
Old		1400	IT

In my report, I have a slicer by country.  I display the data of 1 country at a time.

I need to calculate the % of Sales where Category = 'O' on the total sales of the Segment.

For example, Slicer = FR, For the Segment = 'Old', the measure should return 61.9% ( = 5200 / (5200+3200) )

To do this I tried the following :

CategoryO_%Sales_PerSegment =

DIVIDE (

     CALCULATE (

        SUM ( Table[Sales] )

        ,Table[Category] = "O"

    ),

   CALCULATE (

        SUM ( Table[Sales] )

        , ALL( Table[Category])

    )

)

This does not take in consideration the slicer for country.

Is there a way I can add 

 ALLSELECTED (  Table[Country] ) in addition to ALL( Table[Category]) in the same measure ?

Thank you in advance

[Dangar332 · Accepted solution]

hi, @pr92 

what about other two record old(7500,2400) present with same country(FR)

if it also considerd then use below code

Measure 2 = 
var a = CALCULATE(SUM('Table (2)'[Sales   ]),ALL('Table (2)'[Category   ]))
var b = SUM('Table (2)'[Sales   ])
return
DIVIDE(b,a)

country =fr,segment =old , category=O, 12700/18300=0.69

change measure to percantage

[Anonymous · Accepted solution]

@Dangar332 Thanks for the quick reply.

Hi @pr92 ,

It is possible to use both the 'ALL' function and the 'ALLSELECTED' function in the same measure. But when you apply the 'Country' field to the 'ALLSELECTED' function and no value in the slicer is selected. The 'Country' field loses its filtering effect, which can lead to data errors. If you insist on doing so, I have modified the dax expression you provided.

CategoryO_%Sales_PerSegment = 
VAR _a = SUMX(FILTER('Tabelle1','Tabelle1'[Category] = "O"),[Sales])
VAR _b = DIVIDE( _a,CALCULATE(SUM(Tabelle1[Sales]),ALL(Tabelle1[Category]), ALLSELECTED('Tabelle1'[Country]))
)
RETURN
IF(ISBLANK(_b),BLANK(),_b)

Final output:

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dangar332 · Accepted solution]

hi, @pr92 

what about other two record old(7500,2400) present with same country(FR)

if it also considerd then use below code

Measure 2 = 
var a = CALCULATE(SUM('Table (2)'[Sales   ]),ALL('Table (2)'[Category   ]))
var b = SUM('Table (2)'[Sales   ])
return
DIVIDE(b,a)

country =fr,segment =old , category=O, 12700/18300=0.69

change measure to percantage