DAX Help

[Naveen29]

I have created Matrix table and showing measure (actual %) . Here is sample data and calucaiton. In The final output table I dont want to show the 'Regional All' category . When I filtered out (category = Regional All) ,the actual % is become null.

how to write perfrect DAX to handle this Actual % calucaiton.

Region1	Category	Sales	Calcuation for actual %	Actual %
			= Store1+Store2+Store3/(Region All) for region1	1.22
	Store1	20
	Store2	40
	Store3	50
	Region All	90
Region2			= Store1+Store2+Store3/(Region All) for region2	0.75
	Store1	10
	Store2	20
	Store3	30
	Region All	80

[Anonymous · Accepted solution]

Hi @Naveen29 ，

I guess the reason for this is that the DAX function is affected by an external filter, my suggestion is to use the VAR function to solve this. Variables are evaluated when they are defined, not when they are used. Variables cannot be modified after assignment. We can think of it as a constant.

We can solve this problem by defining "Region All" in measure as a variable and using it later.
Try this measure.

Calcuation for actual % = 
VAR __all =
CALCULATE (
SUM ( 'Table'[Sales] ),
ALLEXCEPT ( 'Table', 'Table'[Region] ),
'Table'[Category] = "Region All"
)
RETURN
DIVIDE ( CALCULATE (
SUM ( 'Table'[Sales] ),
ALLEXCEPT ( 'Table', 'Table'[Region] ),
'Table'[Category] <> "Region All"
), __all )

The result should be like this.

Maybe you want another result like this.

Try this measure.

VAR __all =
CALCULATE (
SUM ( 'Table'[Sales] ),
ALLEXCEPT ( 'Table', 'Table'[Region] ),
'Table'[Category] = "Region All"
)
RETURN
DIVIDE ( SUM ( 'Table'[Sales] ), __all )

Also, attached the pbix file as the reference.

Best Regards,

Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!