Need help in building matrix/card visual with diff aggregation depending on data

[Xeth]

With the following sample data (Blue columns)

• Column Capa has been created as a measure (FC/Plan) and added as a column to the table (to get aggregation)
• Column Utilization has also been created as a measure (Actuals/FC)

I have created a report and want to build a matrix visual as follows...

Challenges I am facing:

• The capa aggregation at level Etype and Cluster is not what I expect, using the measure at the column level in the table
• The utilization aggregation as level Etype and Cluster is not being recalculated but shows summary %
• Unable to get the HC column as I has to count only if the Etype is not Pool and/or if there is non-zero plan/FC
• Likewise with the grand total row.
• Can measures be created for the Capa, Utilization and HC to also display in Card visuals and that get updated depending on the data that is selected (eg. If Cluster is seleted, then data should be conssolidated at this level, likewise for Etype)

[v-tangjie-msft · Accepted solution]

Hi @Xeth ,

According to your description,we can update two measures.

Capa = DIVIDE(SUM('Table'[FC]),SUM('Table'[Plan]))

Utilization = DIVIDE(SUM('Table'[Actuals]),SUM('Table'[FC]))

Then the result is as follows.

As for the HC column, what is your logic and expected outcome?

If I have misunderstood you, please clarify in a follow-up reply. Please provide sample data and your expected result with backend logic and special examples.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.