All Except Issue- Find a % value using ALLEXCEPT

[NavaneethaRaju]

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.

[v-zhangti · Accepted solution]

Hi, @NavaneethaRaju 

As you provide in the figure, you can try the following methods.

Measure:

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

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

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[jdbuchanan71]

OK, leave that filter in but remove the one on owner.

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] )
    )
)

[jdbuchanan71]

@NavaneethaRaju 

What happens if you remove the FILTERS in your CALCULATE.

Total Procedures =
SUMX (
    VALUES ( 'Tag Usage Fact'[Tag ID] ),
    CALCULATE (
        SUM ( 'Procedure Fact'[Procedures] ),
        ALLEXCEPT ( 'Tag Usage Fact', 'Tag Usage Fact'[Tag ID] )
    )
)

[NavaneethaRaju]

@jdbuchanan71 ,

If i write a measure without Filters('Tag Usage Fact'[Order Date]), it will show all the dates of data, so this filter only giving  the selected date range data. I just attached the data above like few rows. is there any measure to solve this issue. Please advice.