Forum Discussion

msprog's avatar
msprog
Icon for Advocate III rankAdvocate III
3 years ago
Solved

Totals on Matrix visual

Hi 

I am looking at the Matrix visual in PBI .

I have a dataset like this ,  This is sample mockup dataset, but the business could put a product into mutiple categories

for e,g Product1 can belong to Cat1 and Cat2

 

I would like this in a matrix visual with the distinct counts for each category. Plus a grand Total 

 

For some reason the total is coming as 6 when it should have been 12. 

Any pointers to how this could be achieved please

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi msprog ,

     

    You can create a measure as 

    Count of Product = var _tab=SUMMARIZE('Table',[Category],"count",DISTINCTCOUNT('Table'[Product]))
return SUMX(_tab,[count])

     

    Best Regards,

    Stephen Tao

     

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

2 Replies

  • Shahfaisal's avatar
    Shahfaisal
    Icon for Solution Sage rankSolution Sage
    3 years ago

    If you create a calculated column by concatenating Category and Product, and then create a distinct count measure using this column, would that give you what you are looking for?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi msprog ,

     

    You can create a measure as 

    Count of Product = var _tab=SUMMARIZE('Table',[Category],"count",DISTINCTCOUNT('Table'[Product]))
return SUMX(_tab,[count])

     

    Best Regards,

    Stephen Tao

     

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