category and sub-category in a table

[IF]

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

[Icey]

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

[mwegener]

Hi @IF ,

that is a matrix.

Check this out.

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

[IF]

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, 

[Icey]

Hi @IF ,

Can you provide some sample data with the same structure as your data table?

Best Regards,

Icey

[IF]

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

[Icey · Accepted 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.

[IF]

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)