Forum Discussion

NavaneethaRaju's avatar
NavaneethaRaju
Helper IV
4 years ago

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 DAX measure

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    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,

    • NavaneethaRaju's avatar
      NavaneethaRaju
      Helper IV
      4 years ago

      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.