Forum Discussion

NavaneethaRaju's avatar
NavaneethaRaju
Helper IV
4 years ago
Solved

All Except Issue- Find a % value using ALLEXCEPT

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 IDSet IDSet NameSystem NameTag Owner Order Owner No of ProceduresTotal Procedures% of UsageUsage Type
297965.905CoalanColanCCGN6875%Shared
297965.905ColanCOlanCCSL2825%Shared
300984.903COALCOALCCGN1425%Shared
300984.903COALCOALGNGN3475%Used
907932.903MARSMARSCSCS3650%Used
907932.903MARSMARSCSIH3650%Shared
While apply one of the option from usage type, this would coming like this.
Tag IDSet IDSet NameSystem NameTag Owner Order Owner No of ProceduresTotal Procedures% of UsageUsage Type
300984.903COAlCOAlGNGN33100%Used
907932.903MARSMARSCSCS33100%Used
 
Actually I need the result like this.
Tag IDSet IDSet NameSystem NameTag Owner Order Owner No of ProceduresTotal Procedures% of UsageUsage Type
300984.903COAlCOAlGNGN3475%Used
907932.903MARSMARSCSCS3650%Used
 
Any one can help me to figure out this.
Need Help
Show and Tell
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    4 years ago

    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's avatar
      NavaneethaRaju
      Helper IV
      4 years ago

      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.

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    4 years ago

    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] )
    )
)
  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    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.