Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Grouping in Matrix is Strange

I have simple dataset and my calculations are around specialty. So I always group results by specialty. When I tried to display the measures I created they show calculations correctly if the first column is Specialty. As soon as I add Provider ID field to the Rows section , measures give the total of all data not per Specialty 

 

The three measures I created are: 
 
Number of Unique Claims COMM = CALCULATE(DISTINCTCOUNT(Sheet1[ClaimNo]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Specialty]),Sheet1[MA/COM]="COM"))
 
 
Number of Unique Claims MA = CALCULATE(DISTINCTCOUNT(Sheet1[ClaimNo]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Specialty]),Sheet1[MA/COM]="MA"))
 
Average CMS = CALCULATE(AVERAGE(Sheet1[Proc Code Industry Distribution/Specialty]),ALLEXCEPT(Sheet1,Sheet1[Specialty]))

 

 

Here is the source file of the report:

 

https://drive.google.com/file/d/1WGDpRR0z-_n7aYzDHYF7IQRdmWtG_1Fg/view?usp=sharing

 

 

 

Am I doing something wrong :( ?

 

Thanks

Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago
    Anonymous wrote:

    d_gosbell  thank you for the reply. Your suggestion will be perfect if my client does not require the Provider ID to be included in the Matrix visual at the beginning before Specialty . 

    Oh, sorry - I think I misunderstood what you were expecting to see in the second table. 

     

    I believe that what you are actually seeing is an issue with ALLEXCEPT where it strips off even implied crossfilters. Probably a safer pattern to use is a combination of ALL and VALUES

     

    eg

     

    Number of Unique Claims COMM = CALCULATE(DISTINCTCOUNT(Sheet1[ClaimNo]),ALL(Sheet1),VALUES(Sheet1[Specialty]),Sheet1[MA/COM]="COM")

     

    The SQLBI guys have a blog on this topic, the last few paragraphs summarize this issue

    https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

5 Replies

  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    7 years ago

    I think you are possibly over complicating things here, I'm not sure why you have added the ALLEXCEPT calls, but I think the calcs will work fine without them. When you have the specialty on the row it will naturally group by that column.

     

    eg.

     

    Number of Unique Claims COMM = CALCULATE(DISTINCTCOUNT(Sheet1[ClaimNo]),Sheet1[MA/COM]="COM")
     
    Number of Unique Claims MA = CALCULATE(DISTINCTCOUNT(Sheet1[ClaimNo]),Sheet1[MA/COM]="MA")
     
    Average CMS = AVERAGE(Sheet1[Proc Code Industry Distribution/Specialty])
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      d_gosbell  thank you for the reply. Your suggestion will be perfect if my client does not require the Provider ID to be included in the Matrix visual at the beginning before Specialty . 

      • d_gosbell's avatar
        d_gosbell
        Icon for Super User rankSuper User
        7 years ago
        Anonymous wrote:

        d_gosbell  thank you for the reply. Your suggestion will be perfect if my client does not require the Provider ID to be included in the Matrix visual at the beginning before Specialty . 

        Oh, sorry - I think I misunderstood what you were expecting to see in the second table. 

         

        I believe that what you are actually seeing is an issue with ALLEXCEPT where it strips off even implied crossfilters. Probably a safer pattern to use is a combination of ALL and VALUES

         

        eg

         

        Number of Unique Claims COMM = CALCULATE(DISTINCTCOUNT(Sheet1[ClaimNo]),ALL(Sheet1),VALUES(Sheet1[Specialty]),Sheet1[MA/COM]="COM")

         

        The SQLBI guys have a blog on this topic, the last few paragraphs summarize this issue

        https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/