Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.