Forum Discussion
Creating a summary table without importing another source.
Hi. I want to achieve this kind of visualisation. However. I don't know how to create a query that will be able to count the number of permanent, contractor, and vacancy per month. I just want to create this summary table from blank query and automatically update the query once another table is updated.
A kind of Table/Query that I want to achieve in PBI without importing an excel as data source. It summarizes the data below. The table should automatically count each contract type per month
Is this possible through DAX measure without importing a data source from excel? The Allocation should automatically contract type per month.
This is the data I have now which will be the basis for an automatic update of summary table above.
This is the visualisation I want to achieve.
Thanks in advance for your help
third_hicana no sorry, with first one I refered to the first code i gave you about the new table.
Create the Table Type with this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKylKTC7JL1LSUTJWitWJVgpILcpNzEvNK4GLhCUmJ+YlV0L4sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, DUMMY = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}})
in
#"Changed Type"then a new table with:
let
Source = Table,
#"Added Custom1" = Table.AddColumn(Source, "DUMMY", each "3"),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"DUMMY"}, Type, {"DUMMY"}, "Type", JoinKind.LeftOuter),
#"Expanded Type" = Table.ExpandTableColumn(#"Merged Queries", "Type", {"Type"}, {"Type.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Type", "Count", each if [Contract Type] = [Type.1] then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Start Date", "Type.1"}, {{"Count", each List.Sum([Count]), type number}})
in
#"Grouped Rows"BBF
23 Replies
third_hicana yes, it is possible. Can you paste some sample data of the table on which calculate the summary one? What does the summary table have to do?
BBF
BeaBF Here's the sampe data.
Resource Name Contract Type Skill Type Maximum Capacity Start Date End Date Status Ana Contractor PM 100% 01/01/2022 04/11/2022 Bart Permanent SPM 100% 01/02/2022 Ongoing Cathy Permanent SPM 90% 01/03/2022 Ongoing Dennis Permanent SPM 70% 01/04/2022 Ongoing Vacant Vacancy PRM 100% No Started Edward Contractor PM 50% 01/04/2022 Ongoing Faye Contractor PM 30% 01/03/2022 Ongoing George Contractor PM 30% 01/04/2022 Ongoing This is the query I want to achieve. It should count the number of each contract type per month without importing a table from excel. I put zero for Ana because her contract was already finished.
third_hicana here the code:
let
Source = Table,
#"Added Custom" = Table.AddColumn(Source, "Count", each if [Status] = "Ongoing" then 1 else 0),
#"Extracted Month Name" = Table.TransformColumns(#"Added Custom", {{"Start Date", each Date.MonthName(_), type text}}),
#"Grouped Rows" = Table.Group(#"Extracted Month Name", {"Start Date", "Contract Type"}, {{"Count", each List.Sum([Count]), type number}})
in
#"Grouped Rows"Table in first line is your native table, from which create the summary one.
BBF
Hi BeaBF So I created this bar graph. But it does not pick up the previous month count for the permanent (btw I changed it to "payroll". Is there any way that the dax can maintain the number of payroll, contractor and vacancy each month? It will just change if there is a date entered in the end date column. Thank you.
For example, if there are 10 contractors on January, it will also appear 10 contractors on February as long as their no date in the end date column.
This is I wish to achieve excluding the line graph.
BeaBF I tried to make a visualisation. However, I can't achieve the second one. I think it is because the counts from previous month does not pick up the next month. Apreciate if you can assist me in achieving the second bar graph.
third_hicana Could you by any chance share the PBI? in order to work in parallel on your own data
BBF
