Forum Discussion
DAX DIVIDE function/expression with one column?
Hello,
I am new to power BI DAX constructs and would like some help. Task is to simply divide items within a single column. Is this possibe? For example - given the grid below, take New lable total purchases for Jan (west region) and divide by new label total purchases for Jan (total region) * 100 to get percentage. Essentially, (3148 / 33888) * 100 = 9.3%. All of the documentation that I've come across requires division to be taken place with two seperate columns. Can you advise on how to express this calculation with the appropriate DAX formula? I greatly appreciate the assist.
| label | Month-of-Purchase | Region | PurchaseWk | Total Puchase |
| New | Jan | West | 2015W02 | 3148 |
| New | Feb | East | 2015W05 | 7195 |
| Moderate | Dec | South | 2015W49 | 1882 |
| Old | Mar | North | 2015W15 | 2779 |
| Old | Mar | Total Region | 2015W15 | 15999 |
| New | Jan | Total Region | 2015W05 | 33888 |
| Moderate | Dec | Total Region | 2016W49 | 44444 |
Thanks!
I think your best bet is going to be to clean your data. As a general rule, you shouldn't have values and aggregated totals in the same column. If you took out the Totals from that column, you could use this DAX:
column= [Total Purchase] / SUM([Total Purchase]) *100
If you're unable to clean the data, I suggest trying the CALCULATE function (click here to read more).
2 Replies
I think your best bet is going to be to clean your data. As a general rule, you shouldn't have values and aggregated totals in the same column. If you took out the Totals from that column, you could use this DAX:
column= [Total Purchase] / SUM([Total Purchase]) *100
If you're unable to clean the data, I suggest trying the CALCULATE function (click here to read more).
Agreed. Best bet certainly looks like cleaning the data. Thanks a ton!
