cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## Calculated row & column in the same matrix

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:

Thanks

1 ACCEPTED SOLUTION
Helper I

i find the solution

4 REPLIES 4
Super User

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.

Helper I

i find the solution

Super User

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.

Helper I

now its ok

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors