Create measure with cumulative objectives

[Anonymous]

Hello,

I am trying to create a cumulative curve graph based on an annual goal calculated based on a number of employees.

Each employee must have at least 3 interviews per year.

For example, if we have 100 employees, we need to reach 300 interviews. To get a monthly goal, I did this calculation: 3 interviews *100 employees /12 months. 

copy of my excel sheet with the data (what I need to create on Power BI).

On Power BI, I'm trying to have a curve chart with cumultaive monthly goal, but the cumulative measure doesn't works .

_OBJ Cumul_VPS = CALCULATE('VPS-Visites'[_Objectif_VPS],
DATESYTD('Date'[Date]))

How can I get this montly cumulative goal, which are not related to a month in the data set ?

Thank you in advance for your help. 

Best regards,

[Anonymous]

Hello Aniya,

Thank you for your answer.

I've tried this solution but it doesn't works.

I'm not advanced on power Bi, that why so I have some questions that will seams easy for you.

1) Where did you save the json files ? The dataset with the objectives needs to be dynamic because it's related to the number of employees. nb of employees*2/12--> Do I need to create my own dataset ?  where shouldI save it ?

2) The measure doesn't show data. I had create the connexion between this table and the calendar table.

Thanks a lot for your help.

Best regards.

[Anonymous]

Hello @v-yueyunzh-msft ,

I am still working on the question I asked a few weeks ago. I'll try to be more clear with my request with this table.

I need  to create a cumulative column of monthly goals based on an annual goal, calculated as follows -> 

_Obj VPS Mensuel = DIVIDE([_Objectif_VPS],12,0) --> Which is the annual objectif I need to report by month

Each employee in the company should have one preventive visit per semester, or two per year.

Obj VPS Monthly :

the other colums are : 

_Total_Employés = COUNT('Liste du personnel'[N° d'empl.]) --> number of employees.

_Objectif_VPS = [_Total_Employés]*2  --> Each employees need to have a visite twice a year. 

_Obj VPS Mensuel = DIVIDE([_Objectif_VPS],12,0) --> Which is the annual objectif I need to report by month.

I need this data to create a line chart with cumulatve objectives.

This is what I currently get on Power BI : 

I also tried to transform the yearly goal by month with the created table but without success :

Thanks a lot for your help.  

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[v-yueyunzh-msft]

Hi , @Anonymous 

Here are the steps you can refer to :

(1)This is my test data :

(2)We need to unpivot the table and add a flag column in Power Query Editor, you can put this in your "Advanced Editor" in your Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nfyUvDNzFPSUTIyJZeI1YlWCssszqxMLVZIKVXIzc8pBkqYQbEhSJWxAYgAsSxAQmAeEJuaweRAUkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [visites = _t, JAN = _t, FEB = _t, MAR = _t, APR = _t, MAY = _t, JUN = _t, JUL = _t, AUG = _t, SEP = _t, OCT = _t, NOV = _t, DEC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"visites", type text}, {"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MAR", Int64.Type}, {"APR", Int64.Type}, {"MAY", Int64.Type}, {"JUN", Int64.Type}, {"JUL", Int64.Type}, {"AUG", Int64.Type}, {"SEP", Int64.Type}, {"OCT", Int64.Type}, {"NOV", Int64.Type}, {"DEC", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"visites"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Attribute] = "JAN" then 1 else if [Attribute] = "FEB" then 2 else if [Attribute] = "MAR" then 3 else if [Attribute] = "APR" then 4 else if [Attribute] = "MAY" then 5 else if [Attribute] = "JUN" then 6 else if [Attribute] = "JUL" then 7 else if [Attribute] = "AUG" then 8 else if [Attribute] = "SEP" then 9 else if [Attribute] = "OCT" then 10 else if [Attribute] = "NOV" then 11 else if [Attribute] = "DEC" then 12 else null, type number)
in
    #"Added Conditional Column"

The table will convert to this:

(3)Then we apply the data to Desktop , and we can create a measure :

Measure = var _current_month = SELECTEDVALUE('Table'[Custom])
var _current_vis =SELECTEDVALUE('Table'[visites])
var _t =FILTER( ALLSELECTED('Table') ,'Table'[Custom] <= _current_month && 'Table'[visites] = _current_vis)
return
SUMX(_t,[Value])

(4)Then we can put the field on the visual we need , and we can sort the "Month" column by the [Custom] column , we can meet your need , the result is as follows:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hello @v-yueyunzh-msft ,

Thanks again.

The measure works well but is not related to the right objectives. 

When I put the measure on the graph, there is no results. 

The column value should be dynamic : nb of employees which is on an other table * Objectives of the year (2 epr month per employees) 

[v-yueyunzh-msft]

Hi , @Anonymous 

Sorry,I don't understand what is "the measure is not related to the right objectives. "

According to your description, do you mean the [value] is generated by other table?

Can you give me sample test data as a table or .pbix file(without sensitive data) or update my test data to me ?

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly