how to merge different calculates in one clustered column chart

[sinanalmac]

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

[v-lili6-msft · Accepted 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

[sinanalmac]

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