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
Baskar
Resident Rockstar
Resident Rockstar

Row level % in Matrix

Thanks Advaced,

 

My prob is have to calculate row level percentage in matrix report. 

 

Capture.PNG

Table Explain:

 

Month from - Month master

Product (A,B,C) from - Product Master

Sales from - Sales master 

 

Question is :

       How to get row level percentage ? using DAX.

 

Thanks

@Sean @ankitpatira @KHorseman @Seth_C_Bauer

 

 

1 ACCEPTED SOLUTION
Baskar
Resident Rockstar
Resident Rockstar

Thanks @Sean

 

I got the solution, i did previously but i forgot it now i got it.

 

anyhow thanks buddy....

 

% Cal = Divide(Sum(Sales[Sales]), Calculate(Sum(Sales[Sales]),All(Date[month]))) 

View solution in original post

7 REPLIES 7
Vvelarde
Community Champion
Community Champion

Hi, this was my solution.
 
My% =
DIVIDE (
    CALCULATE (
        SUM ( Sales[Sales] );
        ALLEXCEPT ( Sales; Sales[Product]; Sales[Month] )
    );
    CALCULATE ( SUM ( Sales[Sales] )ALLEXCEPT ( Sales; Sales[Product] ) )
)
 



Lima - Peru
Sean
Community Champion
Community Champion

If your sales data is formatted as in the picture you posted - obviously you need to Unpivot first!

 

Then just use this measure - you basically need a total for each product which you can get with ALLEXCEPT

EDIT: Overall Total for each Product (Denominator)

 

Measure =
DIVIDE (
    SUM ( Sales[Sales] ),
    CALCULATE ( SUM ( Sales[Sales] ), ALLEXCEPT ( Sales, Sales[Product] ) ),
    0
)

 

% of Row Level.png

Baskar
Resident Rockstar
Resident Rockstar

Hi @Sean

 

Which i shown in picture that is not my data.

 

First 1 is which am getting now in Power BI, Second one is i need.

 

one more info, Product is from different table.

Sean
Community Champion
Community Champion

So how are sales and product connected?

Baskar
Resident Rockstar
Resident Rockstar

Thanks @Sean

 

I got the solution, i did previously but i forgot it now i got it.

 

anyhow thanks buddy....

 

% Cal = Divide(Sum(Sales[Sales]), Calculate(Sum(Sales[Sales]),All(Date[month]))) 

Sean
Community Champion
Community Champion

Okay great!

 

Measure 2 = 
DIVIDE (
    SUM ( Sales[Sales] ),
    CALCULATE ( SUM ( Sales[Sales] ), ALL('Calendar') ),
    0
)

 

% of Row Level2.png

Baskar
Resident Rockstar
Resident Rockstar

I have product ID Relationship

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.