Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have the following dataset:
...and i am trying to merge columns (from Initial Interest Conversion Time) untill PO this Quarter Conversion Time), so then i have the average of each of those columns, per stage, per day..
the end result should look like this more or less:
(i dont need to have all the dates in the calendar; only the dates in the Opportunity Created Date column in the illustration above)
the idea of doing this is to, when building a clustered bar chat, i can have the stages as a Legend with ther respective values.
So if someone have a better idea on how to get this illustration without doing a workaround with the data, it is also valid.
Thank you very very much!
Solved! Go to Solution.
First step: Unpivot your data
let
Source = Csv.Document(File.Contents("Acceleration Example.xlsx - Sheet3.csv"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Opportunity CreatedDate", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Opportunity ID", "Opportunity CreatedDate"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}})
in
#"Changed Type1"
Next, add the required fields to table visual and set aggregation for the value column to Average:
First step: Unpivot your data
let
Source = Csv.Document(File.Contents("Acceleration Example.xlsx - Sheet3.csv"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Opportunity CreatedDate", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Opportunity ID", "Opportunity CreatedDate"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}})
in
#"Changed Type1"
Next, add the required fields to table visual and set aggregation for the value column to Average:
Amazing.
Thank you so much!
OneDrive, Dropbox, GoogleDrive ?
have you decided how to handle null values in your average calculation?
i can share the excel with the dataset with you.
How do i do it?
Put it on OneDrive or Dropbox.
Note: averaging with missing data will result in unreliable output
also, the data is grouped by opportunity, and the nulls mean that the opportunity didnt go through the stage (column). Therefore, it shouldnt be taken into account..
If null, then dont count the value in the average calculation.
I have it on Google drive but need your email to share with you
The NULLs means there was no activity related to this stage, and therefore shouldnt be counted; it is like it doesnt exist
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |