How do I divide using DAX in Power BI?

Question

I have the following columns

Code | Amount
TY123 | 30
TY456 |60
TY789 |80

and so on

I want to find a percentage I have to divide the distinct count of column Code by Total Spends ( Which is coming from a different table ) for each country ( Coming from a different table ) * 100

The resulting table should look like

Country | Code %
A |43%
B | 56%

And so on

How Do I go about this? I am highly confused. I tried the following measure but it is giving me wrong result[error : The function SUM cannot work with TYPE string]

#measure = DIVIDE(SUM('Table'[Code]),SUM('Table2'[Total Spends]))*100

Any help is highly appreciated.

the formula itself looks good to me, the error "The function SUM cannot work with TYPE string" is self-explanatory, one of the columns you use is a string and not a number. Open Power Query and change its data type when you import it — Giovanni Luisotto
Hi, on doing that it is replaced by ERROR and saying Cannot be changed. — coder_bg
(code) column here is of datatype string and that obviuosly cannot be changed....try this calculate(distinctcount(code)/sum(total spends))*100) — user11738502

Alexis Olson Alexis Olson · Accepted Answer · 2020-10-14T13:56:50

Since the code column looks like TY123, that's clearly a string data type column that doesn't make any sense to try to sum or divide.

Since you specify that you want to divide a distinct count of Code by Total Spends, use DISTINCTCOUNT instead of SUM.

measure =
DIVIDE ( DISTINCTCOUNT ( 'Table'[Code] ), SUM ( 'Table2'[Total Spends] ) ) * 100

DISTINCTCOUNT works on any column datatype and is the aggregator you are actually interested in.

How do I divide using DAX in Power BI?

1 Answers