Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Thankyou , it worked ...
@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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |