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,
I have a table and I would like to add categories to the top.
school | 4th grade | 5th grade | 6th grade | 4th grade | 5th grade | 6th grade |
A | 2 | 3 | 6 | 3 | 5 | 6 |
B | 4 | 5 | 6 | 3 | 5 | 8 |
C | 5 | 5 | 6 | 6 | 6 | 7 |
D | 4 | 6 | 8 | 4 | 6 | 7 |
E | 5 | 6 | 7 | 4 | 5 | 8 |
F | 3 | 3 | 7 | 5 | 8 | 8 |
I want to make it in this format:
Male | Female | |||||
school | 4th grade | 5th grade | 6th grade | 4th grade | 5th grade | 6th grade |
A | 2 | 3 | 6 | 3 | 5 | 6 |
B | 4 | 5 | 6 | 3 | 5 | 8 |
C | 5 | 5 | 6 | 6 | 6 | 7 |
D | 4 | 6 | 8 | 4 | 6 | 7 |
E | 5 | 6 | 7 | 4 | 5 | 8 |
F | 3 | 3 | 7 | 5 | 8 | 8 |
Is it possible to do it with the power bi?
Thanks in advance
Solved! Go to Solution.
Hi @IF ,
So, P, R, O, P_, R_, O_ are calculated columns. Right?
If so, try this:
1. Enter data to create a "gendar" table.
2. Create a measure like so:
Measure =
VAR gender_ =
SELECTEDVALUE ( gender[gender] )
VAR grade_ =
SELECTEDVALUE ( gender[grade] )
VAR school_ = SELECTEDVALUE('Table (2)'[school])
RETURN
SWITCH (
gender_,
"Male", SWITCH (
grade_,
"4th grade", SUM ( 'Table (2)'[4th grade] ),
"5th grade", SUM ( 'Table (2)'[5th grade] ),
"6th grade", SUM ( 'Table (2)'[6th grade] )
),
"Female", SWITCH (
grade_,
"4th grade", SUM ( 'Table (2)'[4th grade.1] ),
"5th grade", SUM ( 'Table (2)'[5th grade.1] ),
"6th grade", SUM ( 'Table (2)'[6th grade.1] )
)
)
3. Create a Matrix visual.
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @IF ,
Is this problem solved?
If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let me know.
Best Regards,
Icey
Hi @IF ,
that is a matrix.
Check this out.
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual
Hi,
Thanks for the answer. However the situtation is little more complicated.
I don't have "male" and "female" information in my table. Besides, I display the data by using the logic:
A+B=4th grade
A+C=5th grade
A+D=6th grade
H+E=4th grade_
H+F=5th grade_
H+G=6th grade_
So I have only columns A,B,C,D,E,F,G,H. I want to display each three in a main category. A+B,A+C,A+D will be under male and H+E,H+F, and H+G will be under the female category.
I hope it is possible to do it.
All the best,
Hi @IF ,
Can you provide some sample data with the same structure as your data table?
Best Regards,
Icey
Hi,
Hi,
I wanted to keep it simple, but the exact table is:
XX | YY | A | B | C | D | E | F | G | I | J | K | L | M | N | A2 | B2 | C2 | D2 | P | R | O | P_ | R_ | O_ |
35353 | 2M | 2 | 3 | 4 | 6 | 7 | 5 | 5 | 4 | 6 | 7 | 8 | 9 | 7 | 4 | 6 | 8 | 4 | 22 | 11 | 14 | 6 | 8 | 4 |
35353 | 3M | 4 | 2 | 2 | 5 | 2 | 5 | 5 | 2 | 4 | 4 | 7 | 8 | 8 | 4 | 4 | 4 | 5 | 14 | 9 | 12 | 4 | 4 | 5 |
35354 | 2M | 2 | 3 | 4 | 6 | 7 | 5 | 5 | 4 | 6 | 7 | 8 | 9 | 7 | 4 | 6 | 8 | 4 | 22 | 11 | 14 | 6 | 8 | 4 |
35354 | 7M | 4 | 2 | 2 | 5 | 2 | 6 | 5 | 2 | 4 | 4 | 7 | 8 | 8 | 4 | 4 | 4 | 5 | 15 | 9 | 12 | 4 | 4 | 5 |
35355 | 1M | 2 | 3 | 4 | 6 | 7 | 5 | 5 | 4 | 6 | 7 | 8 | 9 | 7 | 4 | 9 | 8 | 4 | 22 | 11 | 14 | 9 | 8 | 4 |
35355 | 2M | 4 | 2 | 2 | 5 | 2 | 5 | 5 | 2 | 4 | 4 | 7 | 8 | 8 | 4 | 6 | 4 | 5 | 14 | 9 | 12 | 6 | 4 | 5 |
35356 | 2M | 2 | 3 | 4 | 6 | 7 | 5 | 5 | 4 | 6 | 7 | 8 | 9 | 7 | 8 | 6 | 8 | 4 | 22 | 11 | 14 | 6 | 8 | 4 |
35356 | 5M | 4 | 2 | 2 | 5 | 2 | 5 | 5 | 2 | 4 | 4 | 7 | 8 | 8 | 4 | 4 | 4 | 5 | 14 | 9 | 12 | 4 | 4 | 5 |
35360 | 3M | 2 | 3 | 4 | 6 | 9 | 5 | 5 | 4 | 6 | 7 | 8 | 9 | 7 | 4 | 9 | 8 | 4 | 24 | 11 | 14 | 9 | 8 | 4 |
35377 | 1M | 4 | 2 | 2 | 9 | 2 | 8 | 5 | 2 | 4 | 6 | 7 | 8 | 8 | 4 | 4 | 4 | 6 | 21 | 9 | 14 | 4 | 4 | 6 |
35377 | 4M | 2 | 3 | 4 | 6 | 7 | 5 | 9 | 4 | 6 | 7 | 8 | 9 | 7 | 4 | 6 | 8 | 4 | 22 | 15 | 14 | 6 | 8 | 4 |
35377 | 7M | 4 | 2 | 2 | 5 | 2 | 5 | 8 | 2 | 4 | 4 | 7 | 8 | 6 | 4 | 4 | 4 | 5 | 14 | 12 | 10 | 4 | 4 | 5 |
35380 | 1M | 2 | 3 | 4 | 6 | 7 | 5 | 5 | 4 | 6 | 7 | 8 | 9 | 7 | 4 | 6 | 8 | 8 | 22 | 11 | 14 | 6 | 8 | 8 |
35380 | 5M | 4 | 2 | 2 | 5 | 2 | 5 | 5 | 2 | 4 | 4 | 7 | 9 | 8 | 4 | 6 | 4 | 5 | 14 | 9 | 12 | 6 | 4 | 5 |
35387 | 3M | 2 | 3 | 9 | 6 | 7 | 5 | 5 | 6 | 6 | 7 | 8 | 9 | 7 | 4 | 9 | 8 | 4 | 24 | 11 | 14 | 9 | 8 | 4 |
35387 | 7M | 4 | 2 | 2 | 5 | 2 | 5 | 5 | 2 | 4 | 4 | 7 | 8 | 8 | 9 | 4 | 8 | 8 | 14 | 9 | 12 | 4 | 8 | 8 |
35388 | 2M | 2 | 3 | 6 | 6 | 7 | 5 | 5 | 4 | 6 | 9 | 8 | 9 | 7 | 4 | 9 | 8 | 4 | 22 | 11 | 16 | 9 | 8 | 4 |
P is D+E+F+I R IS G+J O IS K+N P_ IS B2 R_ is C2 O_ is D2
I want to group P, R and O as Group1. Similarly I want to group P_, R_ and O_ as Group2. This is going to display my table, which is possible to add a row in excel and type it after merging 3 columns. Is it possible to group them?
Regards
Hi @IF ,
So, P, R, O, P_, R_, O_ are calculated columns. Right?
If so, try this:
1. Enter data to create a "gendar" table.
2. Create a measure like so:
Measure =
VAR gender_ =
SELECTEDVALUE ( gender[gender] )
VAR grade_ =
SELECTEDVALUE ( gender[grade] )
VAR school_ = SELECTEDVALUE('Table (2)'[school])
RETURN
SWITCH (
gender_,
"Male", SWITCH (
grade_,
"4th grade", SUM ( 'Table (2)'[4th grade] ),
"5th grade", SUM ( 'Table (2)'[5th grade] ),
"6th grade", SUM ( 'Table (2)'[6th grade] )
),
"Female", SWITCH (
grade_,
"4th grade", SUM ( 'Table (2)'[4th grade.1] ),
"5th grade", SUM ( 'Table (2)'[5th grade.1] ),
"6th grade", SUM ( 'Table (2)'[6th grade.1] )
)
)
3. Create a Matrix visual.
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I could not open the pbix file. It says: Something went wrong ExecuteXmla failed with result.
BR
Feedback Type:
Frown (Error)
Error Message:
ExecuteXmla failed with result
Stack Trace:
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.ExecuteXmla(String xmla)
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.<>c__DisplayClass41_0.<ImageLoadDatabaseFromPbix>b__0()
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.OnErrorClarify(Action action, String clarification)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
Stack Trace Message:
ExecuteXmla failed with result
Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)
PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.82.5858.961","ModuleName":"Microsoft.PowerBI.Client.Windows.dll","Component":"Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService","Error":"Microsoft.PowerBI.Client.Windows.AnalysisServices.XmlaExecutionException","MethodDef":"ExecuteXmla","ErrorOffset":"112"}
InnerException0.Stack Trace Message:
COM error: Microsoft.MashupEngine, A module named 'BIConnector' hasn't been loaded..
InnerException0.Stack Trace:
InnerException0.Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.GetFeedbackValuesFromException(Exception e, String prefix)
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.GetFeedbackValuesFromInnerExceptions(Exception e, Int32 depth)
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.CreateAdditionalErrorInfo(Exception e)
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)
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 |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |