cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Creating a matrix with multiple measures to display a distribution

Hi all,

I have trouble creating a matrix where I would like to put different measures I calculated to show a distribution.

I have this first table where I can see the names of the people and their score to 3 different tests:

 Name Score1 Score2 Score3 a 80 70 60 b 50 50 50 c 20 30 10 d 80 20 50 e 70 90 80 f 10 10 20

I would like to create a matrix where I can see the distribution of each score under it, that would look like this :

 Score1 Score2 Score3 >=60 50% 33% 33% 40-60 17% 17% 33% <40 33% 50% 33%

For now, I can calculate each of the value, but I don't know how to integrate them in one table.

The measures I use are this, for each score:

Score1 60 and up = CALCULATE(COUNT('Sheet1'[userid]);FILTER('Sheet1';'Sheet1'[Score1]>=60)

Score1 40-60 = CALCULATE(COUNT('Sheet1'[userid]);FILTER('Sheet1';'Sheet1'[Score1]>=40);'Sheet1'[Score1]<60)
Score1 40 and below = CALCULATE(COUNT('Sheet1'[userid]);FILTER('Sheet1';'Sheet1'[Score1]<40)

Does someone know how to do this?

1 ACCEPTED SOLUTION
Solution Sage

Hi @MylèneB ,

In your scenario, we need to unpivot the table using the following query in Edit Queries:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUbIwABLmIMLMQClWJ1opCcg0NUAiQILJQKYRiG8MIgwhgikw7UZIKlNhxlkaQKRBgmkQTVACqDw2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Score1 = _t, Score2 = _t, Score3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Score1", Int64.Type}, {"Score2", Int64.Type}, {"Score3", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"```

Then we will get a table like below:

Then we can create a calculated column using the following DAX query:

```Column =
IF (
'Table'[Value] >= 60,
">=60",
IF ( 'Table'[Value] >= 40 && 'Table'[Value] < 60, "40-60", "<40" )
)```

After that, we can create a measure using the following DAX query:

```Measure 2 =
DIVIDE (
COUNTROWS ( 'Table' ),
CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table'[Column] ) )
)```

Then drag the fields and measure to matrix, we will get a table like below:

Here is the pbix file, please refer to it: https://1drv.ms/u/s!AjytBuzIG5m9ti-8yXcTvKacfZLN

Best Regards,

Teige

2 REPLIES 2
Solution Sage

Hi @MylèneB ,

In your scenario, we need to unpivot the table using the following query in Edit Queries:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUbIwABLmIMLMQClWJ1opCcg0NUAiQILJQKYRiG8MIgwhgikw7UZIKlNhxlkaQKRBgmkQTVACqDw2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Score1 = _t, Score2 = _t, Score3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Score1", Int64.Type}, {"Score2", Int64.Type}, {"Score3", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"```

Then we will get a table like below:

Then we can create a calculated column using the following DAX query:

```Column =
IF (
'Table'[Value] >= 60,
">=60",
IF ( 'Table'[Value] >= 40 && 'Table'[Value] < 60, "40-60", "<40" )
)```

After that, we can create a measure using the following DAX query:

```Measure 2 =
DIVIDE (
COUNTROWS ( 'Table' ),
CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table'[Column] ) )
)```

Then drag the fields and measure to matrix, we will get a table like below:

Here is the pbix file, please refer to it: https://1drv.ms/u/s!AjytBuzIG5m9ti-8yXcTvKacfZLN

Best Regards,

Teige

Helper II

Thank you so much, it worked really well!

As a second step I would want to be able to filter this matrix with slicers.

For example, I have different persons in different groups and I would like to be able to look only at group A and that my distribution matrix is also measuring only group A, is it possible to do this?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors