Forum Discussion
Divide 2 separate rows values in same visual table to get %
Good monring
I hope someone can help me please
I have a table with categories and 2 column values. The column values are a % of the overall column total. The calculation I now need to do is look at the rows values in each column and divide one by the othr to get an additional %. See below example
| Column 1 % | column 2 % | Final Calc % | |
| Cat 1 | 72.08% | 78.05% | 92.35% |
| Cat 2 | 14.06% | 10.06% | 139.76% |
| Cat 3 | 9.71% | 9.02% | 107.65% |
| Cat 4 | 3.44% | 2.87% | 119.86% |
| Total | 100% | 100% |
So the calculation i'm trying to get to is in Final Calc % column. Which is 'column 1 %' (72.08%) Divide By 'Column 2 %' (78.05%) * 100 To get the values in the Final Calc Column of 92.35%
I'm struggling to work out the Dax.
Can someone please help
Many thanks
Karen
Hi KarenFingerhut ,
Since you are trying to do it in a visual I would go for the visual calculation because is much easier to write DAX since it's similar to excel:
- Click on the Visual Calculation on top
- Select Custom
- Add the following code:
Final Calc% = DIVIDE([Column 1 %], [Colum 2%])- Format into percentage using the properties -> data format and selecting the final calc %
Final result:
I made a table based on copying your values directly that is why first column is not 100%.
If you don't want to show the value in the total Final Calc just redo is visual calculation to:
Final Calc% = IF(ISINSCOPE([Cat]), DIVIDE([Column 1 %], [Colum 2%]))
4 Replies
I guess you have tried:
DIVIDE([Measure1],[Measure2],0)*100 ?
You can replace measure1 and Measure2 by sum of column1 and sum of column2
So It gives:DIVIDE(sum('Table'[Column 1 %]),sum('Table'[column 2 %]))*100If your measure are pretty complicated,
I would suggest you to use visual calaculation for this approach
Then, you just have to apply the logic you wantWith this approach, you are not far away from using an excel approach
Hi KarenFingerhut ,
Since you are trying to do it in a visual I would go for the visual calculation because is much easier to write DAX since it's similar to excel:
- Click on the Visual Calculation on top
- Select Custom
- Add the following code:
Final Calc% = DIVIDE([Column 1 %], [Colum 2%])- Format into percentage using the properties -> data format and selecting the final calc %
Final result:
I made a table based on copying your values directly that is why first column is not 100%.
If you don't want to show the value in the total Final Calc just redo is visual calculation to:
Final Calc% = IF(ISINSCOPE([Cat]), DIVIDE([Column 1 %], [Colum 2%]))
The sample data that I used to solve the problem is shown below.Create these 3 Measures
Column 1 % = SUM('Sample Data'[Column 1]) / CALCULATE(SUM('Sample Data'[Column 1]), ALL('Sample Data'))Column 2 % = SUM('Sample Data'[Column 2]) / CALCULATE(SUM('Sample Data'[Column 2]), ALL('Sample Data'))Final Calc %_ = DIVIDE ( [Column 1 %], [Column 2 %] )Steps to Format as Percentage
-
Select the Final Calc % measure in the Fields pane.
-
Go to the Measure tools ribbon (top menu).
-
In the Formatting section:
-
Change Format >> Percentage
-
Set Decimal places >> 2
-
Outcome:
-
Thank you so much for your responses. All sorted, your advice worked a treat. I've learnt somthing new about visual calculations. 😁
Many thanks
Kind rgards
Karen
