Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Publish Measure to chart

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 nameProject On-track Status
Project1On-track
Project2Somewhat on-track
Project3Off-track
Project4On-track
Project5Off-track
Project6On-track
Project7On-track
Project8Off-track
Project9Somewhat on-track
Project10Somewhat on-track
Project11Off-track
Project12On-track
Project13On-track
Project14On-track
Project15On-track
Project16On-track
Project17Somewhat on-track
Project18On-track
Project19On-track
Project20Off-track
Project21Off-track

 

Project name = SUMMARIZE([original parent table],[Project name variable])
Project on-track statusIF([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] = && [SUM Actual NEW] <> 0,1DIVIDE ( [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.
3 REPLIES 3
lbendlin
Super User
Super User

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
Not applicable

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:

JuliusSugi_0-1663640108021.png

project dashboard.pbix 

Thanks in advance!

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.