Forum Discussion
Divide With a Filter
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 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?
If yes, you can create a Measure with following DAX formula.
Percent_Of_Total =
DIVIDE (
SUM ( Table1[Sales] ),
CALCULATE ( SUM ( Table1[Sales] ), ALL ( Table1 ) )
)
Best Regards,
Herbert
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?
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. :smileyhappy:
Best Regards,
Herbert
