Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ashres11
Frequent Visitor

Develop matrix with colum on right

I need to develop matrix table with rightside column. something like following.

Column1Column 2Column3Matrix1Matrix2Matrix 3Matrix 4Column 4Column 5Column 6
CityStateBuilding01/0101/0201/0301/04minmaxavg
santa monicaCaliforniaXYZ St102271572214
          
1 ACCEPTED SOLUTION

Hi @ashres11 

Based on your description, you just need to update the calendar table to the following.

Calendar = ADDCOLUMNS(CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])),"Year-Month",FORMAT([Date],"YYYY-MM"))

2.Change the type table to the following.

Type = var a=SUMMARIZE('Table',[Date])
var b={"Min","Max","Avg"}
return UNION(a,b)

3.Change the measure to the following.

Measure2 =
VAR a =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR b =
    MINX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR c =
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Type'[Type] ),
        "Avg", c,
        "Max", a,
        "Min", b,
        CALCULATE (
            [Sample],
            FILTER ( 'Calendar', FORMAT ( [Date], "" ) IN VALUES ( 'Type'[Type] ) )
        )
    )

Output

vxinruzhumsft_0-1710921336661.png

 

vxinruzhumsft_1-1710921376455.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

11 REPLIES 11
v-xinruzhu-msft
Community Support
Community Support

Hi @ashres11 

You can try the following solution.

Sample data 

vxinruzhumsft_2-1710729449809.png

Sample measure

Sample = SUM('Table'[Value])

1.Create a type table

vxinruzhumsft_1-1710729328850.png

2.Create a measure

Measure2 =
VAR a =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR b =
    MINX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR c =
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Type'[Type] ),
        "Avg", c,
        "Max", a,
        "Min", b,
        CALCULATE ( [Sample], FORMAT ( 'Table'[Date], "" ) IN VALUES ( 'Type'[Type] ) )
    )

Then put the following field to the matrix.

vxinruzhumsft_3-1710729493510.png

Output

vxinruzhumsft_4-1710729500635.png

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

it works now however , I am not able to use my slicer - Jan, 24, feb 24 etc... still it shows data for all months. I have created new column mmm-yy formate but that filter is not applying on this report. rest of other slicer filter works.

Hi @ashres11 

Please refer to the following.

1.Create a calendar table.

Calendar = ADDCOLUMNS(CALENDAR(DATE(2023,1,1),DATE(2023,12,31)),"Year-Month",FORMAT([Date],"YYYY-MM"))

2.Change the type table to the following.

Type = var a=SUMMARIZE('Calendar',[Year-Month])
var b={"Min","Max","Avg"}
return UNION(a,b)

vxinruzhumsft_0-1710832105813.png

3.Create a relationship between calendar table and data table.

vxinruzhumsft_1-1710832133314.png

 

4.Change the measure to the following.

Measure2 =
VAR a =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR b =
    MINX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR c =
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Type'[Year-Month] ),
        "Avg", c,
        "Max", a,
        "Min", b,
        CALCULATE (
            [Sample],
            FILTER ( 'Calendar', 'Calendar'[Year-Month] IN VALUES ( 'Type'[Year-Month] ) )
        )
    )

Output

vxinruzhumsft_2-1710832241468.png

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Thanks for your support.  let say  if I have filter on top where select month such as January 2024 or any other months (iwas able to fix this)

 

Also tomorrow when main table is updated with April, may data, will this dates are dynamically calculated.... or is type table kind of static.

 

Also Type table, date is converted to text so now visual matrix  table is not showing chronological order for date.

 

IN bottom Table should look like

drop down menu somewhere in report and then select January 2024

January 2024
February 2024

 

Once I select January 2024 then it tshould be matrix table with date

CityBuilding01/01/202401/02/202401/03/2024MinMax Avg
ab579597
de111225112516
        

ashres11_0-1710880055723.png

 

Hi @ashres11 

Based on your description, you just need to update the calendar table to the following.

Calendar = ADDCOLUMNS(CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])),"Year-Month",FORMAT([Date],"YYYY-MM"))

2.Change the type table to the following.

Type = var a=SUMMARIZE('Table',[Date])
var b={"Min","Max","Avg"}
return UNION(a,b)

3.Change the measure to the following.

Measure2 =
VAR a =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR b =
    MINX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
VAR c =
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Table' ), [Cat] IN VALUES ( 'Table'[Cat] ) ),
        [Sample]
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Type'[Type] ),
        "Avg", c,
        "Max", a,
        "Min", b,
        CALCULATE (
            [Sample],
            FILTER ( 'Calendar', FORMAT ( [Date], "" ) IN VALUES ( 'Type'[Type] ) )
        )
    )

Output

vxinruzhumsft_0-1710921336661.png

 

vxinruzhumsft_1-1710921376455.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

last piece is how to can be setup ascending order or date and text in specific order non-alphabetical order for Min, max and Avg

 

ashres11_0-1710949767986.png

 

ashres11
Frequent Visitor

I see as following not on right side

 

ashres11_0-1710528560318.png

 

Don't try to make Power BI into something it is not.  Use separate visuals.

 

@gmsamborn was very generous to describe this as tedious. I would have used different wording.

I would try to shrink the column you don't want to see.  For example, if it's [Max1] that you want to hide then you'll have to shrink it below each of the dates.

 

Does that make sense?

 

(I said at the start that it is tedious.)

 

gmsamborn
Super User
Super User

Hi @ashres11 

 

You can achieve that by hiding columns.  It's a tedious work-around but it works.

 

gmsamborn_0-1710523451229.png

 

Custom Matrix Columns.pbix

 

  • For both Values and Column headers, set Text-Wrap to off.
  • Add the measures in the order you want the totals to appear.
  • Shrink the Min, Max, and Avg columns (in the Month columns) until they are invisible.
  • In the column totals, shrink Total.
  • In the column totals, change the Subtotal Label to a space to hide.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors