Measures in another table?

Question

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.

anonymous · Answer

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 · Answer

Hi Lydia,&nbsp;Yep sure - sorry still getting to grips with the site. So I have:&nbsp;Group&nbsp; |&nbsp; Planned Report&nbsp;date &nbsp;|&nbsp; Actual Report date1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 1/1/2018&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 3/3/2018&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; |&nbsp;&nbsp;2/3/2018&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 1/1/2018&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 10/3/2018&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;2/2/2018&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 1/1/2018&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;|&nbsp;&nbsp;2/3/2018&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 1/1/2018&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;|&nbsp;&nbsp;2/3/2018&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;And I want:&nbsp;Group&nbsp; |&nbsp;&nbsp;Report on time&nbsp;%1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 33%2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 100%&nbsp;I have&nbsp;2 more&nbsp;sets of planned and actual columns in the real data for draft report and closing meeting.&nbsp;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?

anonymous · Answer

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.&nbsp;
1-(COUNTBLANK(Planning[Actual_ManagementResponses])/[Total])
In addition, what does the chart look like in Excel?Regards,Lyida

anonymous · Answer

Hi Lydia,&nbsp;Sorry for the delay - been away for a few days.&nbsp;My example table wasn't correct sorry. I've updated it and here's my function again as it relates to my example:&nbsp;1-(CALCULATE(COUNTROWS(Planning),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER(Planning,Planning[Actual Report Date]&gt;Planning[Planned Report Date])) /CALCULATE(COUNTROWS(Planning),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER(Planning,NOT(ISBLANK(Planning[Actual Report Date])))))&nbsp;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).&nbsp;This isn't a huge&nbsp;problem, I was just interested to see if there was a function that would dynamically look down a column and perform&nbsp;a calculation similar to that of my function.

anonymous · Answer

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.&nbsp;
&nbsp;
&nbsp;

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

Forum Discussion

Measures in another table?

6 Replies

Related content

Cumulative Difference from Another measure

Filter measure by another measure

Power Query LookUp in another table

Use measures from another semantic model

[PowerQuery] add (same) column from another table