Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
m1donne
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
Sean
Community Champion
Community Champion

@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
Sean
Community Champion
Community Champion

@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

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

Sean
Community Champion
Community Champion

@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

Fantastic - worked great.

 

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

 

Regards

 

Md

Sean
Community Champion
Community Champion

@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

Thanks Sean

 

Hugely appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.