Table matrix visualization header related query in PowerBI

[deb_power123]

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 :-

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

[v-kkf-msft · Accepted solution]

Hi @deb_power123 ,

Create a new table and relationship like this:

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])
        )
    )

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.

[deb_power123]

Thankyou , it worked ...

[amitchandak]

@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])