cancel
Showing results for
Did you mean:
Post Patron

## Getting the total of a measure column in table as part of a percent calculation for another column

Hi,

I have a table visualisation in my report that contains 3 columns:

1. An asset reference

2. MEASURE: A monetary value column,

3. MEASURE: A conversion of value in column 2 to another currency

I would like a 4th column to show the % of value displayed in column 3 of the total of all values displayed in column 3.

I know that to do this I can use the DIVIDE(col3 / total of column 3). But how do I get the total value of column 3 if it is a measure column?

1 ACCEPTED SOLUTION
Super User

Try like , using the display column to summarize

Divide([Conversion Value], sumx(summarize(Allselected(Fact), Fact[Asset], Fact[Group], "_1",[Conversion Value] ) , [_1]) )

Assuming this is not working

Divide([Conversion Value], calculate([Conversion Value] ) , allselected() ) )

4 REPLIES 4
Super User

@julesdude , Assume column 3 is measure 2

then you can have a measure

divide([Measure], calculate([Measure],allselected()) )

Post Patron

Unfortunately it did not work as I am getting 100% for each row item. It could be I'm not clear on your solution. Here are my column and measure names:

 Asset Group Value [Measure] Conversion Value[Measure] aaa A1 763000000 5410964 bbb A1 81500000 54059432 ccc A1 1170000000 8297284 ddd B2 861000000 6105950 eee B2 149000000 98832582 fff B2 434000000 3077796 ggg B2 610000000 4325934

Essentially I want the collection of assets for each Group to be considered so that I end up getting the following table:

 Asset Group Value Conversion Value Percent Weighting aaa A1 763000000 5410964 7.98% bbb A1 81500000 54059432 79.77% ccc A1 1170000000 8297284 12.24% ddd B2 861000000 6105950 7.39% eee B2 149000000 98832582 5.44% fff B2 434000000 3077796 87.97% ggg B2 610000000 4325934 2.74%

Super User

Try like , using the display column to summarize

Divide([Conversion Value], sumx(summarize(Allselected(Fact), Fact[Asset], Fact[Group], "_1",[Conversion Value] ) , [_1]) )

Assuming this is not working

Divide([Conversion Value], calculate([Conversion Value] ) , allselected() ) )

Post Patron

Many thanks @amitchandak

After a bit of adapting in your first example:

Divide([Conversion Value], sumx(summarize(Allselected(Fact), Fact[Asset], Fact[Group], "_1",[Conversion Value] ) , [_1]) )

....I was able to summarise as desired. Again many thanks for your help on this.