cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

Need to get product result from category filter data in a matrix (5 categories with % data)

Hello Team, good day,

From a Matrix Visual, I need to have last column (CumFPY %) with the product from the 5 categories (percentages are obtained in a measure FPY%).

Please let me know what I need to do?. The % factors are obtained from a filtered data (Category) and raw values are in other table (in relationship with categorized operations). This is a dynamic table with work weeks and "phases" (the 6 rows)

The math formula is: CumFPY % = AI% * Assembly% * FVT% * ICT% * QC%

I hope you can help me with guidance or a DAX if necessary.

Regards,

Gabriel Tapia

Gabriel_Tapia@jabil.com

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III

Re: Need to get product result from category filter data in a matrix (5 categories with % data)

To what I can understand from your image (correct if I'm wrong) you are calculating the FPY% based on the Pass/Test correct so this is a measure?

Believe that what you need is to have a measure similar to this:

``````MEASURE =
CALCULATE ( [FPY%]; FILTER ( ALL ( Table[Category] ); Table[Category] = "AI" ) )
* CALCULATE (
[FPY%];
FILTER ( ALL ( Table[Category] ); Table[Category] = "Assembly" )
)
* CALCULATE (
[FPY%];
FILTER ( ALL ( Table[Category] ); Table[Category] = "FVT" )
)
* CALCULATE (
[FPY%];
FILTER ( ALL ( Table[Category] ); Table[Category] = "ICT" )
)
* CALCULATE ( [FPY%]; FILTER ( ALL ( Table[Category] ); Table[Category] = "QC" ) )``````

Since measures are based on context the calculations are made based on the row Phase and so on.

Regards

Miguel Félix

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

Super User III

Re: Need to get product result from category filter data in a matrix (5 categories with % data)

First of all the comma question is regarding the regional settings if you have the separator as comma on the computer settings PBI works accordingly, on my computer I have semicolon.

Regarding the second question when having more than one value on the matrix the calculations are repeat alongside all columns, the workaround (altough basic) it's to go to each of the columns you don't want to show and reduce them to minimum (like in excel) just drag the column wide until it's not visibile.

Be aware that you first need to turn off word wrap on column headers and values.

Hope this helps and don't forget to mark the correct answer on the post so it can help others.

Regards

Miguel Félix

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

8 REPLIES 8
Super User III

Re: Need to get product result from category filter data in a matrix (5 categories with % data)

How are the other columns in the matrix calculated? Are they columns on your data base or measures?

If they are measures you simply need to make the multiplication of all of them:

``CumFPY % = [AI%] * [Assembly%] * [FVT%] * [ICT%] * [QC%]``

If you are talking about columns you need to have aggregation measures to make the multiplication depending on how you have them aggregated in your matrix.

Is it possible to provide some more information about how you are getting the other columns on the matrix? If you can share a sample file would be even better.

Regards

Miguel Félix

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

Regular Visitor

Re: Need to get product result from category filter data in a matrix (5 categories with % data)

Hello Felix, Good day,

The situation is that I got the categories as filtered from a separated table; where I set the way how they need to be grouped.

Then, there is no any measure or column retriving such specific columns. belo is my Manage relationships and showing that the dynamic values are not retrievable for make the product operation.

Then, If they need to be created how is the correct way to have such data?

I hope it helps to understand this circumstance.

Regards,

Gabriel Tapia

Regular Visitor

Re: Need to get product result from category filter data in a matrix (5 categories with % data)

Hello Felix, good day,

Please let me know how I can upload or send you a sample of the ".pbix" working file?-

Regards,

Gabriel Tapia

Super User III

Re: Need to get product result from category filter data in a matrix (5 categories with % data)

To what I can understand from your image (correct if I'm wrong) you are calculating the FPY% based on the Pass/Test correct so this is a measure?

Believe that what you need is to have a measure similar to this:

``````MEASURE =
CALCULATE ( [FPY%]; FILTER ( ALL ( Table[Category] ); Table[Category] = "AI" ) )
* CALCULATE (
[FPY%];
FILTER ( ALL ( Table[Category] ); Table[Category] = "Assembly" )
)
* CALCULATE (
[FPY%];
FILTER ( ALL ( Table[Category] ); Table[Category] = "FVT" )
)
* CALCULATE (
[FPY%];
FILTER ( ALL ( Table[Category] ); Table[Category] = "ICT" )
)
* CALCULATE ( [FPY%]; FILTER ( ALL ( Table[Category] ); Table[Category] = "QC" ) )``````

Since measures are based on context the calculations are made based on the row Phase and so on.

Regards

Miguel Félix

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

Super User III

Re: Need to get product result from category filter data in a matrix (5 categories with % data)

You can upload the PBIX files by onedrive, googledrive or wetransfer type links. If you want you can send it trough private message if information is sensitive.

Regards

Miguel Félix

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

Regular Visitor

Re: Need to get product result from category filter data in a matrix (5 categories with % data)

Hello Felix, I got it.!!!

I had replaced all commas by semicolon; because DAX expression seems to be sensitive to this.

The calculation is successfully obtained. Thanks a lot.

CALCULATE ( [FPY%], FILTER ( ALL ( AI_ICT_FVT_QC_Assmbly[Category] ), AI_ICT_FVT_QC_Assmbly[Category] = "AI" ) ) * CALCULATE ( [FPY%], FILTER ( ALL ( AI_ICT_FVT_QC_Assmbly[Category] ), AI_ICT_FVT_QC_Assmbly[Category] = "Assembly" ) ) * CALCULATE ( [FPY%], FILTER ( ALL ( AI_ICT_FVT_QC_Assmbly[Category] ), AI_ICT_FVT_QC_Assmbly[Category] = "FVT" ) ) * CALCULATE ( [FPY%], FILTER ( ALL ( AI_ICT_FVT_QC_Assmbly[Category] ), AI_ICT_FVT_QC_Assmbly[Category] = "ICT" ) ) * CALCULATE ( [FPY%], FILTER ( ALL ( AI_ICT_FVT_QC_Assmbly[Category] ), AI_ICT_FVT_QC_Assmbly[Category] = "QC" ) )

But, now I faced other circumstance; I wish to display in Matrix just as single column at rigth end. Placing it in Values field it is displayed as additional column per category.

Can you provide guidance in how to set it Please?

Regards,

Gabriel Tapia

Regular Visitor

Re: Need to get product result from category filter data in a matrix (5 categories with % data)

Hello Felix,

Other circumstance is faced when drill down, the DAX product value is shown only when all 5 categories have a value; but is shown in blank when a category is no having data.

Then, I have such kind of rows that some category is not applicable (and is why there is no data in certain categories). How can be edited the DAX to consider product by row of what is having values?

Regards,

Gabriel Tapia

Super User III

Re: Need to get product result from category filter data in a matrix (5 categories with % data)

First of all the comma question is regarding the regional settings if you have the separator as comma on the computer settings PBI works accordingly, on my computer I have semicolon.

Regarding the second question when having more than one value on the matrix the calculations are repeat alongside all columns, the workaround (altough basic) it's to go to each of the columns you don't want to show and reduce them to minimum (like in excel) just drag the column wide until it's not visibile.

Be aware that you first need to turn off word wrap on column headers and values.

Hope this helps and don't forget to mark the correct answer on the post so it can help others.

Regards

Miguel Félix

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

Announcements

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors