Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

KPI: Value vs target

Can someone please help me, I think there is an easy fix but i dont know how i should fix it.

I've got two views, one with sales data which looks like this:

Nr Date Color Brand Doors Value

1 1-1-2019 Blue BMW 3 20.000

2 1-2-2019 Blue Audi 5 20.000

3 1-3-2019 Green Audi 5 30.000

4 1-1-2019 Red BMW 3 20.000

And anotherone with data from a KPI which looks like this:

Nr: Period: Dimension: DimensionValue: Goal:

1 Jan Color Blue 100.000

2 Jan Brand BMW 100.000

3 Feb Brand Audi 80.000

4 Feb Color Blue 100.000

Can somebody please tell me how i can show the Sum of values and the goal in one table.

This is just a small part of the data structure.

Need Help

v-juanli-msft

7 years ago

Hi Anonymous

To see steps applied in edit queries,

You could paste the following table in excel and save,

Sheet1

Nr	Date	Color	Brand	Doors	Value
1	1/1/2019	Blue	BMW	3	20
2	2/1/2019	Blue	Audi	5	20
3	3/1/2019	Green	Audi	5	30
4	1/1/2019	Red	BMW	3	20

Sheet2

Nr	Date	Color	Brand	Doors	Value
1	1/1/2019	Blue	BMW	3	20
2	2/1/2019	Blue	Audi	5	20
3	3/1/2019	Green	Audi	5	30
4	1/1/2019	Red	BMW	3	20

then open the edit queries in powre bi , create two new blank query, paste the following code

replace the file path with yours

code in Sheet1

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\2\2.1\2.1.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",{{"Nr ", Int64.Type}, {"Date ", type date}, {"Color", type text}, {"Brand", type text}, {" Doors ", Int64.Type}, {" Value", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Nr ", "Date ", " Doors ", " Value"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "a_Value"}}),
    #"Inserted Month Name" = Table.AddColumn(#"Renamed Columns", "Month Name", each Date.MonthName([#"Date "]), type text),
    #"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month Name", each Text.Start(_, 3), type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Extracted First Characters", "Merged", each Text.Combine({[Month Name], [Attribute], [a_Value]}, ""), type text)
in
    #"Inserted Merged Column"

code in sheet2

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\2\2.1\2.1.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Nr: ", Int64.Type}, {"Period:", type text}, {"Dimension: ", type text}, {"DimensionValue: ", type text}, {" Goal:", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[#"Period:"], [#"Dimension: "], [#"DimensionValue: "]}, ""), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Dimension: ", "Dimension:"}, {"DimensionValue: ", "DimensionValue:"}})
in
    #"Renamed Columns"

Best Regards

Maggie

KPI.pbix86 KB

4 Replies

v-juanli-msft

Community Support

7 years ago

Hi Anonymous

Could you show an expected output for me to refer to?

for Date column, is it in format "mm/dd/yyyy" or "dd/mm/yyyy"?

Best Regards

Maggie

Anonymous

Not applicable

7 years ago

Thank you for your reaction on my post.

The date column is formatted "dd/mm/yyyy".

I would like to show the realised sales value compared to the KPI.

Based on the imput data the finished result should look like this (i think):

Nr: Period: Dimension: DimensionValue: Realised Value: Goal:

1 Jan Color Blue 20.000 100.000

2 Jan Brand BMW 40.000 100.000

3 Feb Brand Audi 20.000 80.000

4 Feb Color Blue 20.000 100.000

Kind regards

Maarten

v-juanli-msft

Community Support

7 years ago

Hi Anonymous

To see steps applied in edit queries,

You could paste the following table in excel and save,

Sheet1

Nr	Date	Color	Brand	Doors	Value
1	1/1/2019	Blue	BMW	3	20
2	2/1/2019	Blue	Audi	5	20
3	3/1/2019	Green	Audi	5	30
4	1/1/2019	Red	BMW	3	20

Sheet2

Nr	Date	Color	Brand	Doors	Value
1	1/1/2019	Blue	BMW	3	20
2	2/1/2019	Blue	Audi	5	20
3	3/1/2019	Green	Audi	5	30
4	1/1/2019	Red	BMW	3	20

then open the edit queries in powre bi , create two new blank query, paste the following code

replace the file path with yours

code in Sheet1

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\2\2.1\2.1.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",{{"Nr ", Int64.Type}, {"Date ", type date}, {"Color", type text}, {"Brand", type text}, {" Doors ", Int64.Type}, {" Value", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Nr ", "Date ", " Doors ", " Value"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "a_Value"}}),
    #"Inserted Month Name" = Table.AddColumn(#"Renamed Columns", "Month Name", each Date.MonthName([#"Date "]), type text),
    #"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month Name", each Text.Start(_, 3), type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Extracted First Characters", "Merged", each Text.Combine({[Month Name], [Attribute], [a_Value]}, ""), type text)
in
    #"Inserted Merged Column"

code in sheet2

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\2\2.1\2.1.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Nr: ", Int64.Type}, {"Period:", type text}, {"Dimension: ", type text}, {"DimensionValue: ", type text}, {" Goal:", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[#"Period:"], [#"Dimension: "], [#"DimensionValue: "]}, ""), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Dimension: ", "Dimension:"}, {"DimensionValue: ", "DimensionValue:"}})
in
    #"Renamed Columns"

Best Regards

Maggie

KPI.pbix86 KB

v-juanli-msft
Community Support
7 years ago
Hi Anonymous

Sheet1

Nr Date Color Brand Doors Value

1 1/1/2019 Blue BMW 3 20

2 2/1/2019 Blue Audi 5 20

3 3/1/2019 Green Audi 5 30

4 1/1/2019 Red BMW 3 20

Sheet2

Nr Date Color Brand Doors Value

1 1/1/2019 Blue BMW 3 20

2 2/1/2019 Blue Audi 5 20

3 3/1/2019 Green Audi 5 30

4 1/1/2019 Red BMW 3 20

open Edit queries

in Sheet1

1.

select "Color","Brand", unpivot two columns, thus get columns "Attribute" and "Value", rename this "Value" column as "a_Value"

2.

click on "Date" column, then select "Add column"->"Date"->"month"->"Month name"

in this way i get a column "Month Name"

3.

click on the "Month Name" column, then select "Transform"->"Exact"->"first character"->enter 3,

then column "Month Name" change from all month name to three character format (eg.January->Jan)

4.

click on columns "Month name","Attribute","a_Value" one by one,

then select "Add columns"->merge column->none separator

Finally, Sheet1

In sheet2

5.

click on columns "Period:","Dimension: ","DimensionValue: " one by one,

then select "Add columns"->merge column->none separator

Close&&apply

6.

in Sheet2

create a measure

Measure = IF(MAX(Sheet2[ Goal:])<>BLANK(),SUM(Sheet1[ Value]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

KPI: Value vs target

4 Replies

Related content

KPI showing incorrect target value

Target Value KPI Targets

KPI Visual - target value

KPI values black

KPI Target Only Uses Latest Value