Measures in another table?

[Anonymous]

Hi everyone, still struggling with this measure - hoping someone can help. I'm new to using PowerBI and am starting by building a (what I thought would be) simple dashboard.

I have 4 measures which are counts of non-blank rows in the respective columns over the total rows, e.g.

1-(COUNTBLANK(Planning[Actual_ManagementResponses])/[Total])

I want to display on a bar chart against team (another column in the same query) but the default behaviour is to cluster, which pushes all the bars together when only one team is selected.

In Excel, I can just have a column with 4 numbers and the chart treats it as the same measure (measure names are essentially treated as categories) - do I need to create a calculated table with the measures in each row?

Hope that all makes sense and someone can help. Many thanks for reading.

[v-yuezhe-msft]

@Anonymous,

I am not clear about your expected result. Could you please share dummy data of your table and post expected result here?

Regards,
Lydia

[Anonymous]

Hi Lydia,

Yep sure - sorry still getting to grips with the site. So I have:

Group  |  Planned Report date  |  Actual Report date

1          |  1/1/2018                    |                                      

1          |  3/3/2018                    |  2/3/2018 

2          |  1/1/2018                    |                    

2          |  10/3/2018                  |  2/2/2018     

1          |  1/1/2018                    |  2/3/2018     

1          |  1/1/2018                    |  2/3/2018     

And I want:

Group  |  Report on time %

1          |  33%

2          |  100%

I have 2 more sets of planned and actual columns in the real data for draft report and closing meeting.

I can return the results I want, just wondered if there's a way to do it so it doesn't treat them as 4 measures and cluster?

[v-yuezhe-msft]

@Anonymous,

What logic do you use to calculate the expected result? Using your original DAX, I am unable to get the above pecentage values for Draft%. You don't have blank rows for group 1 and 2 in Draft date. 

1-(COUNTBLANK(Planning[Actual_ManagementResponses])/[Total])

In addition, what does the chart look like in Excel?

Regards,
Lyida

[Anonymous]

Hi Lydia,

Sorry for the delay - been away for a few days.

My example table wasn't correct sorry. I've updated it and here's my function again as it relates to my example:

1-(CALCULATE(COUNTROWS(Planning),
      FILTER(Planning,Planning[Actual Report Date]>Planning[Planned Report Date])
) /
CALCULATE(COUNTROWS(Planning),
      FILTER(Planning,NOT(ISBLANK(Planning[Actual Report Date])))
)
)

I have two similar functions for the other two stages: draft report and closing meeting. These all work fine but the problem is, when I chart them they're clustered and I would like to see them separately. So in Excel, you can effectively treat different measures as categories, assuming their values are of the same type and range, which in this case they are as they're all numbers between 0 and 1 (i.e. percentages).

This isn't a huge problem, I was just interested to see if there was a function that would dynamically look down a column and perform a calculation similar to that of my function.

[v-yuezhe-msft]

---

I have two similar functions for the other two stages: draft report and closing meeting. These all work fine but the problem is, when I chart them they're clustered and I would like to see them separately. 

---

@Anonymous,

Could you please post the screenshot about the chart you create in Power BI Desktop?

You are able to drag all the measures to Value section in the chart as below.


Regards,
Lydia

[Anonymous]

---

@v-yuezhe-msft wrote:

---

I have two similar functions for the other two stages: draft report and closing meeting. These all work fine but the problem is, when I chart them they're clustered and I would like to see them separately. 

---

@Anonymous,

Could you please post the screenshot about the chart you create in Power BI Desktop?

You are able to drag all the measures to Value section in the chart as below.


Regards,
Lydia

---

@v-yuezhe-msft Hiya sorry for the delay. Yes, that's what my chart looks like. I can drag each measure to the Value section.

Desired behaviour would be dynamic measure for % complete, then group by column name. Is that possible?