cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Subtracting columns that have values shown as % of column totals

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]

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Subtracting columns that have values shown as % of column totals

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.

Community Support Team

## Re: Subtracting columns that have values shown as % of column totals

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.

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!