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

[Gabriel_Tapia]

  Hello Team, good day,

  I need your support. 

 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.

Thanks in advance

Regards,

Gabriel Tapia

Gabriel_Tapia@jabil.com

[MFelix · Accepted solution]

Hi @Gabriel_Tapia ,

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.

[MFelix]

Hi @Gabriel_Tapia ,

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.

[Gabriel_Tapia]

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.

Thanks in advance from your support.

Regards,

Gabriel Tapia

[MFelix · Accepted solution]

Hi @Gabriel_Tapia ,

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.

[Gabriel_Tapia]

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

[MFelix]

Hi  @Gabriel_Tapia ,

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.

[Gabriel_Tapia]

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

[MFelix · Accepted solution]

Hi @Gabriel_Tapia ,

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.

[Gabriel_Tapia]

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?

Please check the image.

Thanks in advance.

Regards,

Gabriel Tapia