cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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...

Original Column from Cumulative Column.png

 

Good Luck! Smiley Happy

View solution in original post

6 REPLIES 6
Highlighted
Community Champion
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! Smiley Happy

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
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...

Original Column from Cumulative Column.png

 

Good Luck! Smiley Happy

View solution in original post

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
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

 

Original Column from Cumulative Column 2.png

 

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! Smiley Happy

Highlighted
New Member

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

Thanks Sean

 

Hugely appreciated.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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