cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
deb_power123
Helper V
Helper V

Table matrix visualization header related query in PowerBI

Hi All,

 

I have a table school  with below data in respective columns.

 

I have created a DAX to calculate the %Total Attendance = SUM('school'[Attendance])/SUM('school'[Total Capacity]) * 100

 

This DAX  is used to calculate the Attendance percentage for a particular date.

 

Input data :-

Building Location Weeknumber   Date Attendance Total  Capacity
A  Floor 1     18 03.05.2021     10    20
A  Floor1     18 04.05.2021     12    20
A  Floor 1     18 05.05.2021      8    20
A  Floor2     18 03.05.2021     10    40
A  Floor 2     18 04.05.2021     20    40
A  Floor2     18 05.05.2021     40    40

 

Expected Output :-

Required_Graph.JPG

 

 

I am struggling to get the column sub header, could anyone please suggest how can i achieve this above table matrix in powerBI. The column headers dont detect appropriately in table matrix.

 

Is there any such limitations of the total number of column header to be used in a table matrix? Please suggest

Kind regards

Sameer

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @deb_power123 ,

 

Create a new table and relationship like this:

 

image.pngimage.png

 

Then create measures:

 

M_Attendance = 
var t = 
    IF(
        MAX('Table'[Column]) = "Total Attendance%", 
        FORMAT([%Total Attendance], "Percent"),
        SUM(school[Attendance])
    )
return 
    IF(
        ISFILTERED(school[Location]),
        t,
        IF(
            NOT ISFILTERED(school[Building]),
            [%Total Attendance],
            SUM(school[Attendance])
        )
    )
M_Total Capacity = 
var s = 
    IF(
        MAX('Table'[Column]) = "Total Attendance%",
        BLANK(),
        SUM(school[Total Capacity])
    )
return 
    IF(
        ISFILTERED(school[Location]),
        s,
        IF(
            ISFILTERED(school[Building]),
            SUM(school[Total Capacity])
        )
    )

image.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

3 REPLIES 3
v-kkf-msft
Community Support
Community Support

Hi @deb_power123 ,

 

Create a new table and relationship like this:

 

image.pngimage.png

 

Then create measures:

 

M_Attendance = 
var t = 
    IF(
        MAX('Table'[Column]) = "Total Attendance%", 
        FORMAT([%Total Attendance], "Percent"),
        SUM(school[Attendance])
    )
return 
    IF(
        ISFILTERED(school[Location]),
        t,
        IF(
            NOT ISFILTERED(school[Building]),
            [%Total Attendance],
            SUM(school[Attendance])
        )
    )
M_Total Capacity = 
var s = 
    IF(
        MAX('Table'[Column]) = "Total Attendance%",
        BLANK(),
        SUM(school[Total Capacity])
    )
return 
    IF(
        ISFILTERED(school[Location]),
        s,
        IF(
            ISFILTERED(school[Building]),
            SUM(school[Total Capacity])
        )
    )

image.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

Thankyou , it worked ...

amitchandak
Super User
Super User

@deb_power123 , create a date table and have columns like weeknumber, year, month, day  and use in column and expand all

 

Week Number = WEEKNUM([Date],2)

Year = year([Date])

Month= format([Date],"mmm")

day = Day([Date])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!