Calculated row & column in the same matrix

[Anonymous]

Hello,

i got the following matrix:

I use this measure  in order to calculate the blank rows

Amount_ok = SWITCH(TRUE(),
                Max(BSH[Rank])=3,CALCULATE([Amount1000],FILTER(ALL(BSH),BSH[Rank] in {1,2})),
                Max(BSH[Rank])=7,CALCULATE([Amount1000],FILTER(ALL(BSH),BSH[Rank] in {4,5,6})),
                Max(BSH[Rank])=8,CALCULATE([Amount1000],FILTER(ALL(BSH),BSH[Rank] in {1,2,4,5,6})),
                Max(BSH[Rank])=11,CALCULATE([Amount1000],FILTER(ALL(BSH),BSH[Rank] in {9,10})),
                Max(BSH[Rank])=13,CALCULATE([Amount1000],FILTER(ALL(BSH),BSH[Rank] in {12})),
                Max(BSH[Rank])=14,CALCULATE([Amount1000],FILTER(ALL(BSH),BSH[Rank] in {9,10,12})),
                Max(BSH[Rank])=15,CALCULATE([Amount1000],FILTER(ALL(BSH),BSH[Rank] in {1,2,4,5,6})) - CALCULATE([Amount1000],FILTER(ALL(BSH),BSH[Rank] in {9,10,12})),
                Max(BSH[Rank])=16,CALCULATE([Amount1000]*0.15),FILTER(ALL(BSH),BSH[Rank] in {1,2,4,5,6})) - CALCULATE([Amount1000]*0.15),FILTER(ALL(BSH),BSH[Rank] in {9,10,12})),
                Max(BSH[Rank])=17,CALCULATE([Amount1000]*(1-0.15)),FILTER(ALL(BSH),BSH[Rank] in {1,2,4,5,6})) - CALCULATE([Amount1000]*(1-0.15)),FILTER(ALL(BSH),BSH[Rank] in {9,10,12})),
                [Amount1000]
)

 Now i need to updated this measure in order to derive:

       - a) the third column of the matrix, i.e. Entity_12, which is the sum of the Entity_1 + Entity_2.

       - b) And in the row nr 16 only for the column Entity_12, the formula shouldn't be fixed, but it should be multiply by a parameter, like the one below: 

Max(BSH[Rank])=16,CALCULATE([Amount1000]*max(Query1[ParamCol]),FILTER(ALL(BSH),BSH[Rank] in {1,2,4,5,6})) - CALCULATE([Amount1000]*max(Query1[ParamCol]),FILTER(ALL(BSH),BSH[Rank] in {9,10,12})),

How can I do this?

here  is an example of the model:

https://drive.google.com/file/d/1F7kdItMzoopUx-qv6C5fD-0VHNDo51UJ/view?usp=share_link

Thanks

[AlB]

@Anonymous 

Try this measure that uses the one you had. I am not sure I understand what you want for row nr 16. Check it out and update if  needed ( or elaborate showing with an example and the expected result). See it all at work in the attached file. 

Amount_ok TOT V2 = 
VAR ent1_ = CALCULATE ( [Amount_ok], Entity[DIM_E_DESC] = "Entity_1" , ALL(Entity))
VAR ent2_ = CALCULATE ( [Amount_ok], Entity[DIM_E_DESC] = "Entity_2", ALL(Entity))
RETURN
SWITCH( SELECTEDVALUE(Entity[DIM_E_DESC]),
     "Entity_12",  ent1_ + ent2_,
     [Amount_ok])

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous · Accepted solution]

i find the solution

[AlB]

Hi @Anonymous 

The link provided requires logging into Google. Can you remove that requirement?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

now its ok