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
Anonymous
Not applicable

Divide With a Filter

Quick question.. I'd assume it will be a simple answer. Say I have a table with monthly sales and a table with monthly sales with a visual level filter of some sort.. How could I create a third table showing the percentage of the filtered data of the total amount of sales?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Figured it out.. it was simple as I expected. Still getting comfortable with CALCULATE and FILTER expressions..

 

PercentA = divide(CALCULATE(sum(Sheet1[Sales]),Sheet1[Item Type]="A"),SUM(Sheet1[Sales]))

View solution in original post

6 REPLIES 6
v-haibl-msft
Employee
Employee

@Anonymous

 

If I understand it correctly, you apply the visual level filter on the second table visual. For example, we have 8 months data as table1, and filter the months as table2. You want to get the percent result (72/293, 90/293 and 12/293), is it right?

 

Divide With a Filter_2.jpg

 

If yes, you can create a Measure with following DAX formula.

Percent_Of_Total = 
DIVIDE (
    SUM ( Table1[Sales] ),
    CALCULATE ( SUM ( Table1[Sales] ), ALL ( Table1 ) )
)

Divide With a Filter_3.jpg

 

Best Regards,

Herbert

Anonymous
Not applicable

@v-haibl-msft

Capture.PNG

Not quite... In a very simplistic form, Say I have the data above. Items A and B and their respective sales. I have a table with total sales, and I have a table with total sales, but filtered to item A. I need to create a measure to calculate the % of total sales (302/637) to create a third table.

the terminology can be confusing because there are tables at the data model and then again tables as a visual.

 

As I understand your post: you have 2 data model tables - let's call them 1 & 2.

 

In your visual you want to dynamically filter Table 1 so it just shows the total of whatever item you select i.e. item A? or is this a permanent calculated value at the data model level?

 

Table 2 sum is to be static/fixed?

 

Is the summed amounts the only info that interested you or do you wish to display all the rows that create the sums?

 

www.CahabaData.com
Anonymous
Not applicable

Figured it out.. it was simple as I expected. Still getting comfortable with CALCULATE and FILTER expressions..

 

PercentA = divide(CALCULATE(sum(Sheet1[Sales]),Sheet1[Item Type]="A"),SUM(Sheet1[Sales]))

@Anonymous

 

If you have got your problem answered, you could mark the right answer as solution to close this thread. Smiley Happy

 

Best Regards,

Herbert

bolabuga
Helper V
Helper V

I dont know for sure if this will help you, but there good examples of the use of "divide" with filters in this thread.

 

http://community.powerbi.com/t5/Desktop/Calculation-between-columns-on-a-table/m-p/73322

 

 

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.