Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dripet84
Helper I
Helper I

Calculated row & column in the same matrix

Hello,

i got the following matrix:

 

dripet84_0-1671892454682.png

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

1 ACCEPTED SOLUTION
dripet84
Helper I
Helper I

i find the solution

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

@dripet84 

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])

 

SU18_powerbi_badge

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.

 

dripet84
Helper I
Helper I

i find the solution

AlB
Super User
Super User

Hi @dripet84 

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

 

SU18_powerbi_badge

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.