Line and Histogram visualization of Avg. Price with mixed data structure coming from the CRM system

[AVS218]

Is it possible to generate attached visualisation based on the data table. Some Projects are captured as single line Item and some are multiple line items

@mussaenda 

 

 

Project Name	Contract Start Date	Contract End Date	Contract Term (Years)	Avg. Price per year
Project A	31/03/2021	31/03/2026	5	40000
Project B	25/11/2021	24/11/2026	5	42000
Project C	25/04/2023	24/04/2024	20	50000
Project C	24/04/2024	24/04/2025	20	51000
Project C	24/04/2025	24/04/2026	20	60000
Project C	24/04/2026	24/04/2027	20	60000
Project C	24/04/2027	24/04/2028	20	60000
Project C	24/04/2028	24/04/2029	20	60000
Project C	24/04/2029	24/04/2030	20	60000
Project C	24/04/2030	24/04/2031	20	60000
Project C	24/04/2031	24/04/2032	20	60000
Project C	24/04/2032	24/04/2033	20	60000
Project C	24/04/2033	24/04/2034	20	65000
Project C	24/04/2034	24/04/2035	20	65000
Project C	24/04/2035	24/04/2036	20	65000
Project C	24/04/2036	24/04/2037	20	65000
Project C	24/04/2037	24/04/2038	20	65000
Project C	24/04/2038	24/04/2039	20	66000
Project C	24/04/2039	24/04/2040	20	66000
Project C	24/04/2040	24/04/2041	20	67000
Project C	24/04/2041	24/04/2042	20	67000
Project C	24/04/2042	24/04/2043	20	67000
Project D	31/03/2021	31/03/2026	5	53000
Project E	01/12/2021	01/12/2022	20	75000
Project E	01/12/2022	01/12/2023	20	75000
Project E	02/12/2021	01/12/2024	20	75000
Project E	01/12/2024	01/12/2025	20	75000
Project E	03/12/2021	01/12/2026	20	75000
Project E	01/12/2026	01/12/2027	20	75000
Project E	04/12/2021	01/12/2028	20	75000
Project E	01/12/2028	01/12/2029	20	75000
Project E	05/12/2021	01/12/2030	20	75000
Project E	01/12/2030	01/12/2031	20	75000
Project E	06/12/2021	01/12/2032	20	75000
Project E	01/12/2032	01/12/2033	20	75000
Project E	07/12/2021	01/12/2034	20	75000
Project E	01/12/2034	01/12/2035	20	75000
Project E	08/12/2021	01/12/2036	20	75000
Project E	01/12/2036	01/12/2037	20	75000
Project E	09/12/2021	01/12/2038	20	75000
Project E	01/12/2038	01/12/2039	20	75000
Project E	10/12/2021	01/12/2040	20	75000
Project E	01/12/2040	01/12/2041	20	75000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000
Project F	01/12/2021	01/12/2041	20	59000

[mussaenda]

Hi @AVS218 , 

How come that your Project A and Project B has value on the expected output you provided but on the data you provided, the contract is only 5 years? 

Same with Project D.

Am I missing something?

Thanks

[AVS218]

@mussaenda ...That's how data is captured in CRM system. Some projects are recorded as single line item and some as multiple line because of staggered prices... We need to have visualisation which takes into consideration both scenarios and project expected revenue every year.

[mussaenda]

Hi @AVS218,

let me be clear, 

if the contract year is less than 20 or if the row is single for the project, then I will project the price? Price projected is the last price?

Thanks

[AVS218]

Yes if project is captured single line we project price for number of year's shown i.e. For Project A I'll show €40k 5 times or year 2021 to 2026... For project C I'll use price shown in respective year

[mussaenda · Accepted solution]

Hi @AVS218,

According to your reply, Project A will refrlect 5 times from year 1 to year 5.

But from your expected output, it is reflected from year 1 to year n. This is where I am confused.

Anyhow, I hope this helps you. I think others can still improve my query, but hope that my idea will help you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZQ9a8MwFEX/SvEcsKT79OGxTdO5e8hUunQplP5/ate1fLEV6e21wcMxOtwQDrpeu9evz4/3t++Hx+7UwfYGvTPOMoQR/PiKGZ/udlqdp/Gr8721i+PkD7Ljts55doxMxzA7M8gEZhJ3Q+fduQV8lmxV8gxhkUJ9KTBEpRQZklJKDINSGghgdNJ8bgGrlCyDU0qOAUqJi0AuIviqxEXAKyUuAkEpcRGISomLQFJKXATWIkJV4iLE6CThImQtIlYlLkKcUuIiBPel5+Zd5LF1LuNXY3vrFidD/nVx949ftucyoCG50pIol4TBNySUloJyKTDEhiSlpaRcSgxDQ/KFpfUGqy/9nstgG1IoLSmLABeBVhGxtKQsAlwEWkWk0pKyCHARaBUxlJaURYCLQKMIawpLoixCuAipFPFy745YJT8c0iEd0v+Sbj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Contract Start Date" = _t, #"Contract End Date" = _t, #"Contract Term (Years)" = _t, #"Avg. Price per year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Contract Start Date", type date}, {"Contract End Date", type date}, {"Contract Term (Years)", Int64.Type}, {"Avg. Price per year", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Project Name", Order.Ascending}, {"Contract Start Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Project Name"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"all", each _, type table [Project Name=nullable text, Contract Start Date=nullable date, Contract End Date=nullable date, #"Contract Term (Years)"=nullable number, Avg. Price per year=nullable number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "Index Per Project", 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Contract Start Date", "Contract End Date", "Contract Term (Years)", "Avg. Price per year", "Index Per Project"}, {"Contract Start Date", "Contract End Date", "Contract Term (Years)", "Avg. Price per year", "Index Per Project"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Count] = 1 
then {1..[#"Contract Term (Years)"]}
else {[Index Per Project]}),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom1", "Contract Year", each if [Custom]> 10
then "Year n"
else 

"Year " & Number.ToText([Custom])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Project Name", "Contract Start Date", "Contract End Date", "Contract Term (Years)", "Avg. Price per year", "Custom", "Contract Year"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Other Columns", "Contract Year Index", each if [Contract Year] = "Year n"
and [Contract Year] = "Year n"
then 11 else 

[Custom]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Contract Start Date", type date}, {"Contract End Date", type date}, {"Contract Term (Years)", Int64.Type}, {"Avg. Price per year", Int64.Type}, {"Custom", Int64.Type}, {"Contract Year", type text}, {"Contract Year Index", Int64.Type}})
in
    #"Changed Type1"

[AVS218]

@mussaenda Thanks for your help so far. Was able to crack first solution which is in top graph. For Bottom one I'm struggling how you will achieve breakdown by year for Project A,B, D & F

I added custom column with Date.AddYear function but now landed with nested list in which I can see all values I'm seeking but not able to extract all of them. Not sure whether this is right approach to support in get right line graph for Projects A,B,D & F. Any recommendation @Greg_Deckler  / @mussaenda  / @Anonymous / @amitchandak 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZQ9a8MwFEX/SvEcsKT79OGxTdO5e8hUunQplP5/ate1fLEV6e21wcMxOtwQDrpeu9evz4/3t++Hx+7UwfYGvTPOMoQR/PiKGZ/udlqdp/Gr8721i+PkD7Ljts55doxMxzA7M8gEZhJ3Q+fduQV8lmxV8gxhkUJ9KTBEpRQZklJKDINSGghgdNJ8bgGrlCyDU0qOAUqJi0AuIviqxEXAKyUuAkEpcRGISomLQFJKXATWIkJV4iLE6CThImQtIlYlLkKcUuIiBPel5+Zd5LF1LuNXY3vrFidD/nVx949ftucyoCG50pIol4TBNySUloJyKTDEhiSlpaRcSgxDQ/KFpfUGqy/9nstgG1IoLSmLABeBVhGxtKQsAlwEWkWk0pKyCHARaBUxlJaURYCLQKMIawpLoixCuAipFPFy745YJT8c0iEd0v+Sbj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Contract Start Date" = _t, #"Contract End Date" = _t, #"Contract Term (Years)" = _t, #"Avg. Price per year" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Contract Start Date", type date}, {"Contract End Date", type date}, {"Contract Term (Years)", Int64.Type}, {"Avg. Price per year", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Project Name", Order.Ascending}, {"Contract Start Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Project Name"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"all", each _, type table [Project Name=nullable text, Contract Start Date=nullable date, Contract End Date=nullable date, #"Contract Term (Years)"=nullable number, Avg. Price per year=nullable number, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "Index Per Project", 1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Contract Start Date", "Contract End Date", "Contract Term (Years)", "Avg. Price per year", "Index Per Project"}, {"Contract Start Date", "Contract End Date", "Contract Term (Years)", "Avg. Price per year", "Index Per Project"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Count] = 1
then {1..[#"Contract Term (Years)"]}
else {[Index Per Project]}),
#"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
#"Added Custom2" = Table.AddColumn(#"Expanded Custom1", "Contract Year", each "Year " & Number.ToText([Custom])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Project Name", "Count", "Contract Start Date", "Contract End Date", "Contract Term (Years)", "Avg. Price per year", "Custom", "Contract Year"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Avg. Price per year", type number}, {"Contract Start Date", type date}, {"Contract End Date", type date}, {"Contract Term (Years)", Int64.Type}, {"Custom", Int64.Type}, {"Contract Year", type text}, {"Count", Int64.Type}}),
#"Start Year (Nested)" = Table.AddColumn(#"Changed Type1", "Start Year", each if [Count] = 1 then {if [Custom] = 1 then {[Contract Start Date]} else {Date.AddYears([Contract Start Date],[Custom]-1)}} else {[Contract Start Date]})
in
#"Start Year (Nested)"

[Greg_Deckler]

@AVS218 So, yes, you are going to need something like the following if I am understanding the ask:

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365