Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@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
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.
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😕
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |