Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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() ) )
@julesdude , Assume column 3 is measure 2
then you can have a measure
divide([Measure], calculate([Measure],allselected()) )
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% |
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() ) )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |