Matrix visual - dynamic display

[Boubker]

Hello: 

fact table columns

point name

point id

date

h1

h2

h2

i want a matrix withe following row

Point a sum of hours 

Point b sum of hours 

third row a measure ratio a over b 

column month: so values are monthly 

[v-jingzhan-msft · Accepted solution]

Hi @Boubker 

Here is another approach:

First create two measures to calculate the sum hours for point a and b separately, which are similar to @bhanu_gautam 's suggestions. 

Point a = CALCULATE(SUM(FactTable[h1])+SUM(FactTable[h2])+SUM(FactTable[h3]), FactTable[point name]="point a")

Point b = CALCULATE(SUM(FactTable[h1])+SUM(FactTable[h2])+SUM(FactTable[h3]), FactTable[point name]="point b")

Then create a third measure to calculate the ratio of a to b.

Ratio a/b = DIVIDE([Point a],[Point b])

In the Matrix visual, place all three measures into Values field, place Month column to Column field. Then in Values section of Format pane, turn on Switch values to rows option. 

You will have a matrix similar to below one.

Hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[bhanu_gautam]

@Boubker , You need to create a measure that sums up the hours (h1, h2, etc.) for each point. Assuming you want to sum h1 and h2, you can create a measure like this:

TotalHours = SUM('FactTable'[h1]) + SUM('FactTable'[h2])

Create a measure to calculate the ratio of hours between Point A and Point B. Assuming Point A has PointID = 1 and Point B has PointID = 2, you can create the following measures:

PointAHours = CALCULATE([TotalHours], 'FactTable'[PointID] = 1)

Add a new matrix visual to your report.
Drag the Date field to the columns section of the matrix to display the data by month.
Drag the Point Name field to the rows section of the matrix.
Add the TotalHours measure to the values section of the matrix.
To add the ratio, you can create a new table or use a calculated column to display the ratio in the matrix.
PointBHours = CALCULATE([TotalHours], 'FactTable'[PointID] = 2)
RatioAOverB = DIVIDE([PointAHours], [PointBHours], 0)

Ensure the matrix is formatted to display the data as required, with the points and their respective sums of hours, and the ratio in the third row.

If you need more details share sample data