Divide 2 separate rows values in same visual table to get %

[KarenFingerhut]

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

[KarenFingerhut]

@rohit1991 @MFelix @Cookistador 

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

[rohit1991]

Hi @KarenFingerhut 

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:

[MFelix · Accepted solution]

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%]))

[Cookistador]

Hi @KarenFingerhut 

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 %]))*100

If 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 want

With this approach, you are not far away from using an excel approach