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
bryanrendra
Helper II
Helper II

MATRIX INCORRECT SUBTOTAL BECAUSE ADVANCE NESTED MEASURE

Hi I really need your help, I would like to calculate percentage from 2 calculated measure. however the percentage measure is not summing each row percentage instead applying the formula on the subtotal here is the document:

book.jpg

 The percentage is showing 10% because the formula applied there , instead i just want to calculate 6%+3%+6$%+4%+8%+5%+4%+4%+5%+7%+4%+4%+3%=63%

here is the formula for percentage:

Percentage = IF(
DIVIDE(([LatestPrice Excise]-[PreviousPrice Excise]),[PreviousPrice Excise],0)=0,0,IF([LatestPrice Excise]=0,0,ABS((DIVIDE(([LatestPrice Excise]-[PreviousPrice Excise]),[PreviousPrice Excise],0)))))
 
the reason i do IF statement because the latest price of the book might be 0 because there is not stock on warehouse.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@bryanrendra , Try this out

DIVIDE(abs(([LatestPrice Excise]-[PreviousPrice Excise])),[PreviousPrice Excise],0)

 

Or Sumx(Values(Table[Group]),[Percentage])

 

Here group is your rows/axis/group by you used

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@bryanrendra , Try this out

DIVIDE(abs(([LatestPrice Excise]-[PreviousPrice Excise])),[PreviousPrice Excise],0)

 

Or Sumx(Values(Table[Group]),[Percentage])

 

Here group is your rows/axis/group by you used

Hi your solution is fully work, thank you so much Mr. Amitchandak

thank you for your answer, but it gave me 0 % to the all of the rows

Maybe you could try this:

Percentage =
SUMX (
    VALUES ( Table[ColumnInYourVisual] ),
    IF (
        [LatestPrice Excise] = 0,
        0,
        DIVIDE (
            ABS ( [LatestPrice Excise] - [PreviousPrice Excise] ),
            [PreviousPrice Excise],
            0
        )
    )
)
lbendlin
Super User
Super User

Read up on the concepts of SUMX and HASONEVALUE.

I am really new to power bi, could you help me please?

 

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.