need help with calculation

[zoupie]

We want to calculate the % completed each month and use this value in the line value of a combination bar and line chart.  I only see the option to show the % of grand total in the quick measures.  New to PowerBI. 

sample

SO_START_DATE	SO_JOB_SEQ	SO_STATUS_NAME
1/12/2020 6:00	12155	SkipCond
1/12/2020 6:00	12624	SkipCond
1/4/2020 6:00	16890	Skip!RunCal
1/4/2020 6:00	12624	SkipCond
1/4/2020 6:00	15694	SkipCond
1/4/2020 6:00	12155	SkipCond
1/4/2020 6:00	12624	SkipCond
1/4/2020 6:00	9177	SkipCond
1/4/2020 10:00	8855	SKIPPED
1/4/2020 11:00	8855	SKIPPED
1/4/2020 11:30	8856	SKIPPED
1/4/2020 11:36	15573	FINISHED
1/4/2020 13:00	8856	SKIPPED
1/4/2020 13:06	15573	FINISHED
1/8/2020 21:15	15573	FINISHED
1/8/2020 21:16	25448	FINISHED
1/8/2020 21:18	15573	FINISHED
1/8/2020 21:21	25448	FINISHED

[v-jayw-msft]

Hi @zoupie ,

Create a calculated column yearmonth as x-axis.

yearmonth = FORMAT('Table'[SO_START_DATE],"YYYYMM")

Create measure as below.

Measure = CALCULATE(SUM('Table'[SO_JOB_SEQ]),FILTER(ALLEXCEPT('Table','Table'[yearmonth]),'Table'[SO_STATUS_NAME]="FINISHED"))/CALCULATE(SUM('Table'[SO_JOB_SEQ]),ALLEXCEPT('Table','Table'[yearmonth]))

Check if the result is what you want.

If not, let me know.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zoupie]

sorry new to this formula builder.  I am trying yearmonth.  not working

yearmonth = FORMAT('vw_datamart_Activities'[dt_start],"YYYYMM")

This is more of the downsteam table

feel free to adjust our dashboard.  AppWorx_Azure

[v-jayw-msft · Accepted solution]

Hi @zoupie ,

Check the document about how to create calculated column.

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns

In addition, I don't quite understand what your mean "This is more of the downsteam table".

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zoupie]

looking for monthly % time status is finished

[v-jayw-msft]

Hi @zoupie ,

Normally we can use ALLEXCEPT() function to calculate values group by category.

https://docs.microsoft.com/en-us/dax/allexcept-function-dax

Better to share some sample data to us for further help if you don't have any Confidential Information.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

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