cancel
Showing results for
Did you mean:
Highlighted
New Member

## Calculating the total of a column and a new column showing % of the total

Hi there

I have a table containing sales units (sales data[sales units]), I would like to create another column which shows the percentage % of each sale against the total sales.

I'm not sure whether I need to create a measure for total sales and then divide the (sales data[sales units]) by the measure.......but I could be completely wrong.

Any help/guidance would be greatfully appreciated.

Regards

Md

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Champion

## Re: Calculating the total of a column and a new column showing % of the total

@m1donne

Okay I had to add an Index Column in the Query Editor to the table

(Query Editor => Add Column tab => Index Column => From1 => Close & Apply)

And then use this Index to create another COLUMN

```Orignal Data COLUMN =
VAR ThisPeriod = 'Table'[Index]
RETURN
'Table'[Cumulative Column]
- CALCULATE (
SUM ( 'Table'[Cumulative Column] ),
FILTER ( 'Table', 'Table'[Index] = ThisPeriod - 1 )
)```

Here's the result... Good Luck! 6 REPLIES 6
Highlighted Community Champion

## Re: Calculating the total of a column and a new column showing % of the total

@m1donne

here's the COLUMN formula that will calculate this

```% of Total Units COLUMN =
DIVIDE ( 'Sales Data'[Sales Units], SUM ( 'Sales Data'[Sales Units] ), 0 )```

and the MEASURE

```% of Total Units MEASURE =
DIVIDE (
SUM ( 'Sales Data'[Sales Units] ),
CALCULATE ( SUM ( 'Sales Data'[Sales Units] ), ALLSELECTED ( 'Sales Data' ) ),
0
)```

Hope this helps! Highlighted
New Member

## Re: Calculating the total of a column and a new column showing % of the total

That worked great - thank you.

My next xhallenge is to take a column with a cumulative set of values, and break it down into their non-cumulative values........any ideas?

Regards

Md

Highlighted Community Champion

## Re: Calculating the total of a column and a new column showing % of the total

@m1donne

Okay I had to add an Index Column in the Query Editor to the table

(Query Editor => Add Column tab => Index Column => From1 => Close & Apply)

And then use this Index to create another COLUMN

```Orignal Data COLUMN =
VAR ThisPeriod = 'Table'[Index]
RETURN
'Table'[Cumulative Column]
- CALCULATE (
SUM ( 'Table'[Cumulative Column] ),
FILTER ( 'Table', 'Table'[Index] = ThisPeriod - 1 )
)```

Here's the result... Good Luck! Highlighted
New Member

## Re: Calculating the total of a column and a new column showing % of the total

Fantastic - worked great.

Can you please explain in laymans terms what the index and subsuquest formula does?

Regards

Md

Highlighted Community Champion

## Re: Calculating the total of a column and a new column showing % of the total

@m1donne

Okay here's an example with a Date Column instead of an Index

(because most likely you have a Date Column and its easier to understand)

```Orignal Data COLUMN =
VAR CurrentDate = 'TableName'[Date]
VAR PreviousDate =
CALCULATE (
MAX ( TableName[Date] ),
FILTER ( 'TableName', TableName[Date] < CurrentDate )
)
RETURN
TableName[Running Total]
- CALCULATE (
SUM ( TableName[Running Total] ),
FILTER ( 'TableName', 'TableName'[Date] = PreviousDate )
)```

Here's the result DAX works on Tables and Columns NOT on Cells (like Excel)

so you need Context and that's why you need an Index or the Date Column!

Hope this makes sense!

Good Luck! Highlighted
New Member

## Re: Calculating the total of a column and a new column showing % of the total

Thanks Sean

Hugely appreciated.

Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 #### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más! Top Solution Authors
Top Kudoed Authors
Users online (843)