How to apply a calculation to sum of value in a Matrix control based on the grouping category

[westerdaled]

As part of a dashboard design , I have imported 2 Excel tables into Power BI desktop from the same worksheet

I have replicated the pivot tables for each Excel table using a Matrix visual. Each Matrix conists of 3 colums, Category, Accounting Period of Sum of Value. Expanding the Category, I can see for each quarter, the relevant sum value.  Now the hard bit, If I want use the Category to apply a weighting to each sum, then this seems imposible say trying to with a Measure:  I cannot use say the SWITCH command on the Category, as it won't understand the context, in the model. Is there anyway around this.

[Greg_Deckler]

@westerdaled Hard to be sure without sample data to mock up but you should be able to apply the Category as a weight by just using MAX('Table'[Category]) and applying the appropriate weight. See if this gives you some ideas.

Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community

[westerdaled]

@Greg_Deckler 

Thanks for getting back to me . Unfortunately, I don't have access to the this Report; I am assisting the staff who do. That said, I should be able to mock up the Excel Table used in the transformation import.

I can clarify what format cagegory is in: essentially, it is "AA-BB-CC" or "AAAA-BB-CC" . The calculation applied to the Sum of Value is determi ed by the first set of characters before the delimiter. I think the MAX('Table'[Category]) only works with a numeric value.  

[westerdaled]

@Greg_Deckler 

I have been looking through your code sample and I think you do have an interesting approach as regards the weighting, which I will try:

IF( MAX(Table[Category)="AA-BB-CC", [SumOfTotal) * x, If(( MAX(Table[Category])="AA-BB-DD"[SumOfTotal) * x)))

I am still trying to get my head around Max() with alphanumeric argument😕