Subtracting columns that have values shown as % of column totals

[omitche2]

Hi,

I’ve created calculated columns to show market share for the current year and the prior year as a percent of the column total.  I’d like to show the difference between these two columns but am not getting what I want.  For instance, if the cy ms is 42.27% and the py ms was 44.06% I’d like the difference to be 1.79% instead I’m getting the difference of .29%. 

Here is my dax:

CY = divide(Query1[sales19],

    calculate(sum(Query1[sales19]),ALLSELECTED()),0)

PY = divide(Query1[sales18],

    calculate(sum(Query1[sales18]),ALLSELECTED()),0)

CHANGE = Query1[CY]-Query1[PY]

[v-juanli-msft · Accepted solution]

Hi @omitche2 

In your case, are these columns or measures?

Query1[sales19], Query1[sales18],

Could you show me your table struture so to provide a more clear solution?

Regarding comparation between the current year and the prior year, you could use DAX function as suggested below:

https://community.powerbi.com/t5/Desktop/Current-and-previous-year-comparison/td-p/155503

https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/dax-query-language-for-...

If you don't have date column in your table, only have "year", "month" columns, you could refer to the following thread:

https://community.powerbi.com/t5/Desktop/Current-Year-and-previous-year/td-p/629767

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.