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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PK_PBIBoss
Frequent Visitor

Matrix showing weird behavior while adding fields under Rows :-

I have a table and need to apply sensitivity on Year 2020 it works fine with all values coming :-

 

Base Data  - 

GroupFinancial Item201820192020
OINet Interest177179164
OIFee7911
TIOper Inc184188175
TIInc All-7-44
TotalTotal Inc177184179

If I dont use the group column and create the matrix with slicers itworks fine :-

 

 Slicer Net IntSlicerFee  
 25%-15%  
Financial Item201820192020Rev-2020
Net Interest177179164205
Fee79119.35
Oper Inc184188175214.35
Inc All-7-444
Total Inc177184179218.35

 

Measure Formula :- 

WB_APAC_RFC_Perct =
VAR X1 = DIVIDE(
CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Net Interest"),
CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Operating Income"),1)

VAR X2 = DIVIDE(
CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Fee"),
CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Operating Income"),1)

VAR X3 = DIVIDE(
CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Other"),
CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Operating Income"),1)

RETURN
SUMX(WB_APAC, IF(WB_APAC[Financial Item] = "Net Interest" , WB_APAC[RFC 2020] * (1+ 'Net Interest Parameter'[Net Interest Parameter Value]),
IF(WB_APAC[Financial Item] = "Fee" , WB_APAC[RFC 2020] * (1+ 'Fee Parameter'[Fee Parameter Value]) ,
IF(WB_APAC[Financial Item]= "Operating Income" , WB_APAC[RFC 2020] + (((X1*WB_APAC[RFC 2020]) * 'Net Interest Parameter'[Net Interest Parameter Value]) + ((X2*WB_APAC[RFC 2020]) * 'Fee Parameter'[Fee Parameter Value]) 8),WB_APAC[RFC 2020])))))

 

But when I add group to matrix :- The Operating Income does not get calculated :-

 

  Slicer Net IntSlicerFee  
  25%-15%  
GroupFinancial Item201820192020Rev-2020
OINet Interest177179164205
OIFee79119.35
TIOperating Income184188175175
TIIncome Allocated-7-444
 Total177184179179

 

2 REPLIES 2
v-lionel-msft
Community Support
Community Support

Try this measure.

WB_APAC_RFC_Perct = 
VAR X1 = 
DIVIDE(
    CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Net Interest"),
    CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Operating Income"),
    1
)

VAR X2 = 
DIVIDE(
    CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Fee"),
    CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Operating Income"),
    1
)

VAR X3 = 
DIVIDE(
    CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Other"),
    CALCULATE(SUM(WB_APAC[RFC 2020]),WB_APAC[Financial Item]="Operating Income"),
    1
)

RETURN
SUMX(
    ALL(WB_APAC), 
    IF(
        WB_APAC[Financial Item] = "Net Interest" , 
        WB_APAC[RFC 2020] * (1+ 'Net Interest Parameter'[Net Interest Parameter Value]),
        IF(
            WB_APAC[Financial Item] = "Fee" , 
            WB_APAC[RFC 2020] * (1+ 'Fee Parameter'[Fee Parameter Value]) ,
            IF(
                WB_APAC[Financial Item]= "Operating Income" , 
                WB_APAC[RFC 2020] + (  X1*WB_APAC[RFC 2020] * 'Net Interest Parameter'[Net Interest Parameter Value] + X2*WB_APAC[RFC 2020] * 'Fee Parameter'[Fee Parameter Value] ),
                WB_APAC[RFC 2020]
            )
        )
    )
)

If the correct value is still not returned, this may be a problem with the following code.

v-lionel-msft_0-1594796524555.png

Do you mind sharing your .pbix file?

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

amitchandak
Super User
Super User

@PK_PBIBoss , You should Unpivot the table and then you should use it.

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.