New value in the same column and arrange the status column in a required sequence

[Anonymous]

Hi All,

I have a requirement which i need to add Total in status column (Total = Appr+Up - Dis) 

and other requirment is to arrange columns sequance in state ,Total,Appr,Up,Dis 

please find more details below data set 

[BekahLoSurdo]

Hi @Anonymous,

I would pivot this in Power Query first to get the format that you would like:

let
Source = Excel.Workbook(File.Contents("C:\Users\rlosurdo\Desktop\Sample Pivot.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"State", type text}, {"Status", type text}, {"Sales", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Status]), "Status", "Sales", List.Sum),
#"Added Total" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum({[Appr],[Up],-[Dis]})),
#"Changed Total Type" = Table.TransformColumnTypes(#"Added Total",{{"Total", Int64.Type}})
in
#"Changed Total Type"

Then you can use a table instead of a matrix to have more control over column order, etc.:

[Anonymous]

Hi,

Thanks alot for your response, it worked well

if i want have Distinct count on IDs what would be the approch here for Pivoting 

example :

[Ashish_Mathur]

Hi,

Create a matrix visual and drag State to the row labels, Status to the column labels.  Write this measure

Count = DISTINCTCOUNT(Data[SalID])

Hope this helps.

[Anonymous]

thanks for your replay, here we have a trick 

i need to create a custom calculation column is total = Appr - DIs 

and also we need to reaarnge columns Appr then Dis and Up 

so direct matrix table wont help - because total is custom column and also arranging columns in specific order .

please let me know any other ways 

thanks

[Ashish_Mathur]

Hi,

This requirement is very different from what you posted 2 hours ago - there you only wanted to show a distinct count.  Share a dataset and show your expected result.

[Anonymous]

Hi , thanks for your prompt response.

Please find below data set and expected output :

[Ashish_Mathur · Accepted solution]

Hi,

Download my PBI file from here.

Hope this helps.

[Anonymous]

Hi @Anonymous,

It's better to create individual calculated measure for each Status and drag them into Tabe visual, So that you will have more control to show what you expecting.

Please let me know if you have any questions, happy to help you.

Regards,

Pavan Vanguri.