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
mrvamsidhar
Helper I
Helper I

Calculate Total and %

Hello, I am working on Power-Bi Dashboard to set up simple table using below. I need to show % of each product and total sales in a Month. I am having Table1. My output should be Table 2. 

 

Here is what I am thinking.

  1. Create a measure to identify total sales in Month.
  2. DIVIDE [Actual sales by product] with [1].

For (1), I added thismeasure TotalSales = CALCULATE(SUM('Table'[Sales]),GROUPBY('Table','Table'[Month])). But this is not working.

If i am selecting only month column then , i see my measure is working. But If i add product it is not working. My Total sales column should display total sales in month having Month and Product columns in my view.

Any help or any better idea?

 

Table1. Data Available

 

MonthProductSales
JUNEA100
JUNEB120
JUNEC20
JULYA30
JULYB150
JULYC80

 

Table2. Data with New measures.

 

MonthProductSalesTotal Sales% Sales
JUNEA10024041.67%
JUNEB12024050.00%
JUNEC202408.33%
JULYA3026011.54%
JULYB15026057.69%
JULYC8026030.77%
1 ACCEPTED SOLUTION

Thanks. I figured it out. CALCULATE(SUM('Table'[Sales]),GROUPBY('Table','Table'[Month]),ALL('Table')).

 

thats it. - I need to tell to DAX exclude all filters. so ALL() did the trick.

View solution in original post

2 REPLIES 2
masitbaha
Regular Visitor

How about using 'Matrix' instead of 'Table'?

Thanks. I figured it out. CALCULATE(SUM('Table'[Sales]),GROUPBY('Table','Table'[Month]),ALL('Table')).

 

thats it. - I need to tell to DAX exclude all filters. so ALL() did the trick.

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.