Publish Measure to chart

[Anonymous]

Hi everyone,

I need your advice on how I can publish a measure that generate from other measure into donut chart. This is what my report table (matrix) look like generated from thousands of data.

Project name	Project On-track Status
Project1	On-track
Project2	Somewhat on-track
Project3	Off-track
Project4	On-track
Project5	Off-track
Project6	On-track
Project7	On-track
Project8	Off-track
Project9	Somewhat on-track
Project10	Somewhat on-track
Project11	Off-track
Project12	On-track
Project13	On-track
Project14	On-track
Project15	On-track
Project16	On-track
Project17	Somewhat on-track
Project18	On-track
Project19	On-track
Project20	Off-track
Project21	Off-track

Project name = SUMMARIZE([original parent table],[Project name variable])

Project on-track status = IF([Project Achievement]>=0.90,"On-track",IF([Project Achievement]>0.70 && [Project Achievement]<0.90,"Somewhat on-track",IF([Project Achievement]<0.70,"Off-track","No data")))

Project Achievement = DIVIDE (COUNTROWS ( FILTER ( Indicator, [Achievement measure] >= 0.9 && [SUM Target NEW]<>0), COUNTROWS ( FILTER ( Indicator, [SUM Target NEW] <> 0 ) ), [Indicator])

Achievement measure = IF (HASONEVALUE ( Indicator[Indicator] ),IF ([SUM Target NEW] = 0 && [SUM Actual NEW] <> 0,1, DIVIDE ( [SUM Actual NEW], [SUM Target NEW] )))

Is there any possibilities that I can publish measure "Project on-track status"? I understand that I need to have dimension and the measure is unable to be published as an axis. However, I don't know what other step that I need to build to achieve this. 

Appreciate your help and kindess. Thank you in advance.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Hi @lbendlin 

Thank you for your attention. Let me post my pbi file. This chart what exactly I need that coming from project on-track status measure:

project dashboard.pbix 

Thanks in advance!

[lbendlin]

The difference between calculated columns and measures is subtle but important. Calculated columns are onl computed once and are independent of filter choices. Measures are computed dynamically each time a filter changes. 

One could argue that your process status should be a calculated column as it cannot be impacted by filter choices.  On the other hand there could be screnarios where the status can flip based on resource allocation etc.  So let's assume that using measures is appropriate.

You will need to predefine your buckets in a disconnected table. The number is added to facilitate the sort order.

Status = {("On-track",1),("Somewhat on-track",2),("Off-track",3),("No data",4)}

Then you need to create a measure that counts the matches.

Measure 2 = 
var a = ADDCOLUMNS(values(IPTT_Data[Project name]),"t",[Project On-track])
return countrows(filter(a,[t]=SELECTEDVALUE('Status'[Value])))

This is horribly inefficient but gives you the required result.

Note that you can also simplify your measure somewhat

Project On-track = SWITCH(TRUE(),[Project Achievement no-zero]>=0.90,"On-track",[Project Achievement no-zero]>0.70,"Somewhat on-track",[Project Achievement no-zero]<0.70,"Off-track","No data")

 see attached.