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
Solved! Go to Solution.
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. |
i find the solution
Hi @dripet84
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. |
now its ok
User | Count |
---|---|
135 | |
62 | |
57 | |
57 | |
46 |
User | Count |
---|---|
131 | |
62 | |
58 | |
56 | |
50 |