Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Matrix visual - dynamic display

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  colu...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous 

     

    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's avatar
bhanu_gautam
Super User
1 year ago

Anonymous , 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