Calculation

[Anonymous]

Hi Team,

I have some data shown below.

Month	Folio starts	Folio ends	Product	Code	Disposal	Working capacity	Tank ratio
Feb	20220201	20220229	X	T-001	2,275,000	3,288,103	0.69
March	20220301	20220332	X	T-001	3,974,000	3,288,103	1.21
April	20220401	20220431	X	T-001	2,868,000	3,288,103	0.87
May	20220501	20220532	X	T-001	2,339,000	3,288,103	0.71
Feb	20220201	20220229	Y	T-002	2,152,000	2,681,141	0.8
March	20220301	20220332	Y	T-002	1,600,000	2,681,141	0.6
April	20220401	20220431	Y	T-002	1,636,000	2,681,141	0.61
May	20220501	20220532	Y	T-002	1,578,000	2,681,141	0.59
Feb	20220201	20220229	Z	T-003	1,555,000	2,674,702	0.58
March	20220301	20220332	Z	T-003	2,972,000	2,674,702	1.11
April	20220401	20220431	Z	T-003	3,982,000	2,674,702	1.49
May	20220501	20220532	Z	T-003	2,265,000	2,674,702	0.85
Feb	20220201	20220229	W	T-004	1,930,000	3,898,786	0.5
March	20220301	20220332	W	T-004	2,496,000	3,898,786	0.64
April	20220401	20220431	W	T-004	2,076,000	3,898,786	0.53
May	20220501	20220532	W	T-004	3,604,000	3,898,786	0.92

I need output like this.

Feb					7,912,000	12,542,732	0.63
March					11,042,000	12,542,732	0.88
April					10,562,000	12,542,732	0.84
May					9,786,000	12,542,732	0.78

so the disposal is sum of all the months.When comes to work capacity it is not summing correctly.While taking this ratio in the bar graph it is adding all the values because the work capacity is same.If I am trying to write the measure it is adding all the values.

It is working fine on the Matrix table visual.When comes to Bargraph it is not showing correctly.

Could anyone please help me on this.

[v-yiruan-msft · Accepted solution]

Hi  @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

Ratio = 
VAR _selmonth =
    SELECTEDVALUE ( 'Table'[Month] )
VAR _disposal =
    CALCULATE ( SUM ( 'Table'[Disposal] ), ALLSELECTED ( 'Table'[Month] ) )
VAR _wc =
    CALCULATE ( SUM ( 'Table'[Working capacity] ), ALLSELECTED ( 'Table'[Month] ) )
RETURN
    DIVIDE ( _disposal, _wc )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Nathaniel_C]

Hi @Anonymous 

Please provide your pbix.

If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel