topic Re: All Except Issue- Find a % value using ALLEXCEPT in DAX Commands and Tips

All Except Issue- Find a % value using ALLEXCEPT

NavaneethaRaju — Mon, 08 Aug 2022 14:09:02 GMT

Hi,

I created a measure for sum the total for respective of tag, So i got a total values of indiviual tag id.there i used all except measure to create the total for the respective tag ID. after that i want the find the percentage of the total.
Below i mentioned the measure
1.

Total Procedures =

SUMX (

VALUES ( 'Tag Usage Fact'[Tag ID] ),

CALCULATE (

SUM ( 'Procedure Fact'[Procedures] ),

ALLEXCEPT (

'Tag Usage Fact',

'Tag Usage Fact'[Tag ID]

FILTERS ( 'Tag Usage Fact'[Order Date] ),FILTERS('Tag Usage Fact'[Tag Owner Name])

)

)
2.

% =SUM('Procedure Fact'[Procedures])/[Total Procedures] .

And Now i want to filter the table like only who all are marked as Used or Shared. there i'm facing the issue. If i the filter the used data % of the particular tag is showing 100%, actually it should give the % value from the total. I realy don't know how to filter the data without affecting Total Procedures Measure.

I'm enclosing the sample data. for your reference.

Tag ID	Set ID	Set Name	System Name	Tag Owner	Order Owner	No of Procedures	Total Procedures	% of Usage	Usage Type
297	965.905	Coalan	Colan	CC	GN	6	8	75%	Shared
297	965.905	Colan	COlan	CC	SL	2	8	25%	Shared
300	984.903	COAL	COAL	CC	GN	1	4	25%	Shared
300	984.903	COAL	COAL	GN	GN	3	4	75%	Used
907	932.903	MARS	MARS	CS	CS	3	6	50%	Used
907	932.903	MARS	MARS	CS	IH	3	6	50%	Shared

While apply one of the option from usage type, this would coming like this.

Tag ID	Set ID	Set Name	System Name	Tag Owner	Order Owner	No of Procedures	Total Procedures	% of Usage	Usage Type
300	984.903	COAl	COAl	GN	GN	3	3	100%	Used
907	932.903	MARS	MARS	CS	CS	3	3	100%	Used

Actually I need the result like this.

Tag ID	Set ID	Set Name	System Name	Tag Owner	Order Owner	No of Procedures	Total Procedures	% of Usage	Usage Type
300	984.903	COAl	COAl	GN	GN	3	4	75%	Used
907	932.903	MARS	MARS	CS	CS	3	6	50%	Used

Any one can help me to figure out this.

Re: All Except Issue- Find a % value using ALLEXCEPT

amitchandak — Mon, 08 Aug 2022 15:50:44 GMT

NavaneethaRaju , Have you tried

divide(SUM('Procedure Fact'[Procedures]), calculate(SUM('Procedure Fact'[Procedures])), all())

or

divide(SUM('Procedure Fact'[Procedures]), calculate(SUM('Procedure Fact'[Procedures])), allselected())

Re: All Except Issue- Find a % value using ALLEXCEPT

v-yalanwu-msft — Thu, 11 Aug 2022 06:28:08 GMT

Hi， NavaneethaRaju ；

You could create a measure.

Total Procedures = CALCULATE(SUM('Tag Usage Fact'[No of Procedures]),ALLEXCEPT('Tag Usage Fact','Tag Usage Fact'[Tag ID]))

% of Usage = SUM('Tag Usage Fact'[No of Procedures])/[Total Procedures]

The final show:

Best Regards,

Re: All Except Issue- Find a % value using ALLEXCEPT

NavaneethaRaju — Thu, 11 Aug 2022 13:26:04 GMT

Hi v-yalanwu-msft ,

I also used this measure before, this measure only showing all total,
but actually the total should be respective of the selected date range. without that Filters(Order date) i can't display the total for selected date range.