Create measure % of total

[nirrobi]

Hi all,

I have table like the bellow.

I need to add measure name "import % of Total" that calculate for each row import / total total (50/483, ... 230/483 etc.)

How can you build this measure?

(Import is sum of column from one table

Export is sum of column from other table 

Total is the measure that sum import+export)

Customer	Import	Export	Total	Import % of Total
AAA	50	55	105	10%
BBB	60	66	126	12%
CCC	70	77	147	14%
DDD	50	55	105	10%
Total	230	253	483	48%

Many thanks.

Nir

[Haegi]

Hello,

First you can create an calculated column "BigTotal" = SUM(Total).

Calculated column don't work with context so it will be the sum of all rows of youre dataset.

Next you can create a new measure "Import ratio" = Import/BigTotal

Hope it's help you.

[nirrobi]

Thanks for your reply.

I did not manage to accomplish my task 😞

I manage to create one big table with my data and face similar problem to my opinion.

I need to have table/matrix like the one below but I cannot succeed to create the last column.

Hope someone can help.

Regards,

Cumm %	% of total	AMOUNT	CLIENT
17%	17%	100	AAA
50%	33%	200	BBB
100%	50%	300	CCC
100%	100%	600	TOTAL

[nirrobi]

I still struggle with this problem.

anyone? please...

[allaboutdata]

@nirrobi did you try using what i had above? What are your problems now?

[nirrobi]

I tried and succeed many thanks.

can you pleaes help me with the below problem:

I need to create cumulative percentage in simple table.

Cumm %	% of total	AMOUNT	CLIENT
17%	17%	100	AAA
50%	33%	200	BBB
100%	50%	300	CCC
100%	100%	600	TOTAL

[Haegi]

Hello,

To calculate a cumulative percent.

You can use again to DAX formula to create a new measure.

I not sure but you can try a solution like this

Cumm = CALCULATE(SUM([%Total]), FILTER(ALL([Customer]), [%Total] <= 1))

Regards

[allaboutdata]

Hi Nirrobi,

not sure where you got with this. I couldn't get exactly what you were looking for but this seems to be the best solution at the moment.

Import % Cumu* Total = var bigTotal = SUMX(ALL(Sheet1[CustID]), CALCULATE(SUM(Sheet1[Import])))
                     return DIVIDE(SUMX(FILTER(All(Sheet1[CustID]), Sheet1[CustID] <= MAX(Sheet1[CustID])),CALCULATE(SUM(Sheet1[Import]))),bigTotal)

You'll notice i had to use a customer Id which was an integer id representation of each customer. This is because the DAX <= operator cannot compare two strings. I thought sort by might be an option but the operator doesn't seem to call through to that.

This results with a table like below:

The first line of code:

var bigTotal = SUMX(ALL(Sheet1[CustID]), CALCULATE(SUM(Sheet1[Import])))

Like prior calculates the overall total.

The filter command in the second line:

FILTER(All(Sheet1[CustID]), Sheet1[CustID] <= MAX(Sheet1[CustID]))

Tells the calculation to only look at customer ids which less than the one you're currently looking at in the calculation.

Coupled with SUMX and then doing the division.

Let me know if that works.

Note if you want to use it in a table with the other measure created earlier you will need to create a measure based on that ID instead. 

Import % of Total CustID = var bigTotal = SUMX(ALL(Sheet1[CustID]), CALCULATE(SUM(Sheet1[Import]) + SUM(Sheet1[Export])))
                    return DIVIDE(SUM([Import]),bigTotal)

[nirrobi]

any one?

please...

[allaboutdata · Accepted solution]

Hey Nirrobi,

can you try this?

Import % of Total = var bigTotal = SUMX(ALL(Sheet1[Customer]), CALCULATE(SUM(Sheet1[Import]) + SUM(Sheet1[Export])))
                    return DIVIDE(SUM([Import]),bigTotal)

Basically

ALL(Sheet1[Customer])

Forces it to do the calculation across all your customers. 

CALCULATE(SUM(Sheet1[Import]) + SUM(Sheet1[Export])

Says to do the sum of Import and Export on a row by row basis i.e. the total

Wrapped with sumx it translates to, for all customers calculate the total on a row by row basis and then add together all the totals to get your Total of all totals.

Your % of total then just becomes:

DIVIDE(SUM([Import]),bigTotal)

This is the output that i get:

[sdukes_88]

Can anyone help me?

I follow this but everytime my totals are 100%

[nirrobi]

Hi,

In which step you stucked?

never mind 🙂 here is summarize of this post:

I created the table as I mention in the first topic of this thread:

than I create a measure as follow:

Total ALL =
CALCULATE ( SUM ( Table1[Total] ), ALL ( Table1 ) )

Measure Import % Of Total =
DIVIDE ( SUM ( Table1[Import] ), [Total ALL] )

Than with new feature call "quick measure" create the last measure:

Measure Import % Of Total running total in Customer =
CALCULATE (
    'Table1'[Measure Import % Of Total],
    FILTER (
        ALLSELECTED ( 'Table1'[Customer] ),
        ISONORAFTER ( 'Table1'[Customer], MAX ( 'Table1'[Customer] ), DESC )

Hope it will help you.

Nir