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
Year | Organization | Category | A | B | A/B |
2017 | ORG03 | DES | 947.139.990 | 1.012.789.990 | 0,94 |
2018 | ORG03 | DES | 1.032.033.340 | 1.123.163.340 | 0,92 |
2017 | ORG03 | DRES | 22.583.395.150 | 23.415.332.170 | 0,96 |
2018 | ORG03 | DRES | 19.020.697.500 | 19.676.907.500 | 0,97 |
2017 | ORG01 | FO | 71.992.082.110 | 78.812.484.870 | 0,91 |
2018 | ORG01 | FO | 61.018.822.630 | 69.095.907.880 | 0,88 |
2018 | ORG03 | FO | 5.320.000 | 5.380.000 | 0,99 |
2017 | ORG01 | AC | 143.056.467.959 | 155.319.215.800 | 0,92 |
2018 | ORG01 | AC | 128.905.806.910 | 141.928.239.100 | 0,91 |
2017 | ORG04 | IC | 466.541.114.830 | 466.958.939.830 | 1,00 |
2018 | ORG04 | IC | 453.199.936.300 | 453.345.342.970 | 1,00 |
2017 | ORG01 | DC | 67.099.256.750 | 78.393.077.360 | 0,86 |
2018 | ORG01 | DC | 60.848.380.880 | 71.946.366.910 | 0,85 |
2017 | ORG03 | SAV | 6.418.922.520 | 6.824.522.520 | 0,94 |
2018 | ORG03 | SAV | 6.529.833.330 | 6.868.713.330 | 0,95 |
2017 | ORG03 | SCC | 3.289.763.330 | 3.532.763.330 | 0,93 |
2018 | ORG03 | SCC | 2.977.010.000 | 3.206.440.000 | 0,93 |
2017 | ORG01 | TE | 45.920.635.980 | 50.427.961.990 | 0,91 |
2018 | ORG01 | TE | 35.587.456.090 | 39.073.626.090 | 0,91 |
2017 | ORG03 | TE | 5.183.040.020 | 5.641.960.020 | 0,92 |
2018 | ORG03 | TE | 4.398.580.630 | 4.636.480.630 | 0,95 |
I can do some calculates about organizaon , Category, and Year
I just want merge this calculates in one clustered column chart
For example
ORG01 CATEGORY A/B + ORG03 and ORG04 Totals A/B + YEARS TOTAL+ AB
Yon find example picture and power bI file in the below link
picture
https://1drv.ms/u/s!AtrShuJq6Tbz70llIW8XSxEvFWiv
PBI file
https://1drv.ms/u/s!AtrShuJq6Tbz70qlvBFnkjJDMuQ3
Solved! Go to Solution.
hi, @sinanalmac
After my research, you could do these as below:
If you just want to merge ORG01 CATEGORY A/B + ORG03 and ORG04 Totals A/B
That's easy to do
In Edit Queries, Select column Organization and Category then click Transform->Unpovit Columns
Close&Apply
Create a new a/b measure
NEW A/B = SUM('CCF (2)'[A])/SUM('CCF (2)'[B])
Then drag the field Value into Axis
for YEARS TOTAL+ AB it has no Axis so it needs a separate clustered column chart
Or you could add a axis for it like below:
Add a Axis table with append a row of “total”
this I use Union Function create it.
Then create the relationship with unpivot table
then add a new measure as below:
Measure 2 = IF(CALCULATE(MAX('Table'[Value]),ALL('CCF (2)'[Year]))="total",CALCULATE(SUM('CCF (2)'[A]),ALLEXCEPT('CCF (2)','CCF (2)'[Value]))/CALCULATE(SUM('CCF (2)'[B]),ALLEXCEPT('CCF (2)','CCF (2)'[Value])),SUM('CCF (2)'[A])/SUM('CCF (2)'[B]))
Drag the filed into chart
here is pbix, please try it.
https://www.dropbox.com/s/h8e7exc7lpss5sj/allinone.pbix?dl=0
Best Regards,
Lin
hi, @sinanalmac
After my research, you could do these as below:
If you just want to merge ORG01 CATEGORY A/B + ORG03 and ORG04 Totals A/B
That's easy to do
In Edit Queries, Select column Organization and Category then click Transform->Unpovit Columns
Close&Apply
Create a new a/b measure
NEW A/B = SUM('CCF (2)'[A])/SUM('CCF (2)'[B])
Then drag the field Value into Axis
for YEARS TOTAL+ AB it has no Axis so it needs a separate clustered column chart
Or you could add a axis for it like below:
Add a Axis table with append a row of “total”
this I use Union Function create it.
Then create the relationship with unpivot table
then add a new measure as below:
Measure 2 = IF(CALCULATE(MAX('Table'[Value]),ALL('CCF (2)'[Year]))="total",CALCULATE(SUM('CCF (2)'[A]),ALLEXCEPT('CCF (2)','CCF (2)'[Value]))/CALCULATE(SUM('CCF (2)'[B]),ALLEXCEPT('CCF (2)','CCF (2)'[Value])),SUM('CCF (2)'[A])/SUM('CCF (2)'[B]))
Drag the filed into chart
here is pbix, please try it.
https://www.dropbox.com/s/h8e7exc7lpss5sj/allinone.pbix?dl=0
Best Regards,
Lin
Hi @v-lili6-msft,
Thank you for your Reply. I tried your solution. And ıt's done. ıt has given me many good ideas.
Best Regards
Sinan
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |