Forum Discussion

sneidercub's avatar
sneidercub
Frequent Visitor
4 years ago
Solved

Array to display the total category group

Hello Community,

So I have this database with about 2 million rows structured like that.


Category.png

So what I want to do is show them in a matrix like this.

Category Result.png

I was trying to add the word "Total" as another category and calculate the values with a new measure.
This in order to add that new measure as the "Values" for the matrix, but I still did not get any success.


Could you help me with some ideas so I can do it?

Need Help
Tutorial Requests
  • danextian's avatar
    danextian
    4 years ago

    Hi sneidercub ,

    I've updated the Sum with Total measure to:

     

    Sum with Total = 
VAR __One =
    SELECTEDVALUE ( Category[Category] )
RETURN
    IF (
        __One = "Total"
            || NOT ( HASONEVALUE ( Category[Category] ) ),
        CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table'[Category] ) ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[Category] = __One )
        )
    )

     

     

    The rest involves formatting the matrix visual. However, selectively removing the subtotal for each item in a column is not currently supported.

    You may refer to the same link for the updated pbix.

     

6 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    4 years ago

    Hi sneidercub ,

    My approach would be to create a disconnected table (no relationship with other tables) and use that to hold the values needed.

     

    Calculated table:

    Category = 
VAR __T1 =
    DISTINCT ( 'Table'[Category] )
RETURN
    UNION ( __T1, ROW ( "Category", "Total" ) )

    Measure:

    Sum with Total = 
VAR __CATEGORY =
    SELECTEDVALUE ( Category[Category] )
RETURN
    IF (
        __CATEGORY = "Total",
        CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table'[Category] ) ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[Category] = __CATEGORY )
        )
    )

    Output:

    Sample PBIX: https://drive.google.com/file/d/1Rnlav71Z0idt6lVRmyI70kjI6JgBLrqe/view?usp=sharing 

    • sneidercub's avatar
      sneidercub
      Frequent Visitor
      4 years ago

      That's great! 

      Thank you for your help!

      I think I may have simplified too much the issue, let me try to show a little more about what I'm working on:

      so this is a "better" view for the data base:

       besides year there are like other 4 or 5 "categories" that I would have to take in consideration, and the final view I want to get should look more like this:



      and taking into consideration it's a 2 million rows database I'm not sure if creating a new disconnected table with a cross join will help.

      Could you help me with your approach to this?

      Thank you again danextian 

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        4 years ago

        Please post  a sample data that can be easily copy-pasted.