Grouping in Matrix is Strange

[Anonymous]

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

[d_gosbell]

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]

@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 · Accepted solution]

---

@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/

[Anonymous]

Thank you @d_gosbell  for the formula. it works.. can you please explain to me what VALUE function does it this context? 

Thank you again 

[d_gosbell]

---

@Anonymous wrote:

Thank you @d_gosbell  for the formula. it works.. can you please explain to me what VALUE function does it this context? 

---

The VALUES function brings back the list of distinct specialities in the current filter context. So the ALL(Sheet1) strips all filters from the Sheet1 table and VALUES(Sheet1[Specialty]) then brings back just the current distinct set of Specialities.