Putting a matrix on a matrix (or: Counting the results of an existing Matrix)

[Thomas-Frank]

Here is the result that I would like to generate.

Any ideas?

(I looked through some existing questions, but the answers there were above my head ... also with more complex topics)

BR

Thomas

[v-karpurapud]

Hi @Thomas-Frank 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @Thomas-Frank 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @Thomas-Frank 

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank You!

[Thomas-Frank]

Thank you. Let me digest it 🙂

[sjoerdvn]

I see something missing in my example measure:
NUMBER_OF_SALES_EMPLOYEES =
VAR npc = SELECTEDVALUE(NUMBER_OF_PCs_BY_PERSON[NUMBER_OF_PCs_BY_PERSON])
RETURN COUNTROWS(FILTER(VALUES(Table[Sales Employee Name]),[DISTINCT_COUNT_PCS]=npc)

[sjoerdvn]

So my guess is that DISTINCT_COUNT_PCS is a measure that you want to use as a (dimension) column. For this you will need a helper table 

create a table

NUMBER_OF_PCs_BY_PERSON =
SELECTCOLUMNS(GENERATESERIES(0, MAXX(VALUES(Table[Sales Employee Name]),[DISTINCT_COUNT_PCS]),1), "NUMBER_OF_PCs_BY_PERSON",[Value])

create measures
NUMBER_OF_SALES_EMPLOYEES =
VAR npc = SELECTEDVALUE(NUMBER_OF_PCs_BY_PERSON[NUMBER_OF_PCs_BY_PERSON])
COUNTROWS(FILTER(VALUES(Table[Sales Employee Name]),[DISTINCT_COUNT_PCS]=npc)

[Thomas-Frank]

thanks for the quick answer!

I played around a bit and managed to build a new TABLE (Table 2) with the desired output results. 

However, If I understood it correctly, this new table (Table 2) cannot be filtered again based on dimensions that I had in my Raw Data Table (Table 1), e.g. like "country" or "profit Center (see fitler on the below picture). 

I am looking for a way to basically have Table 2, but still being able to filter Table 2 for a) country or b) profit center, data which I had in table 2, and then see the (adjusted) output again in Table 2. 

Hope it makes sense what I am writing.

Any thoughts?

BR

Thomas

[DataNinja777]

Hi @Thomas-Frank ,

o create "Matrix 2" in Power BI based on the results of "Matrix 1," we need to count how often each DISTINCT_COUNT_PCS value appears across all employees. This requires aggregating occurrences of DISTINCT_COUNT_PCS values and displaying them in a separate matrix.

One way to achieve this is by using a DAX measure that counts the occurrences of each DISTINCT_COUNT_PCS value in the dataset. The following measure accomplishes this:

Frequency Count = 
VAR PCSCounts = VALUES('Table'[DISTINCT_COUNT_PCS])
RETURN 
    COUNTROWS(
        FILTER(
            ALL('Table'), 
            'Table'[DISTINCT_COUNT_PCS] IN PCSCounts
        )
    )

Alternatively, if you prefer to create a separate table rather than using a measure, you can use the SUMMARIZE function to generate a new table that groups DISTINCT_COUNT_PCS values and counts their occurrences:

Matrix2_Table = 
SUMMARIZE(
    'Table',
    'Table'[DISTINCT_COUNT_PCS],
    "Frequency Count", COUNT('Table'[Sales Employee Name])
)

In Power BI, you can use the first approach by adding the measure to your matrix visualization, or the second approach to create a new summarized table and display it in a matrix. This will ensure that you get a count of how many times each DISTINCT_COUNT_PCS value appears across all employees, matching the "Frequency Count" column shown in your desired output.

Best regards,