Percent Measure, by Month across 2 tables giving higher than 100%

[Lenihan]

I keep getting stumped unfortunately. 

I have 2 tables. A full list of all configuration items (text field) in one table, and a second table which is a list of config items that were updated last month. I just need a simple % of Config items updated last month.

The two tables are "Config Metrics - Updated" (the ones updated last month) and "All CIs" .

I created a new measure

% Updated = (count('Config Metrics - Updated'[CI Identifier]) / count('All CIs'[CI Identifier])) and put in format %. If I add that number to the chart or table, it comes out correct - around 9%

I then try to apply this to a line chart, so that I can have the x-axis as the month. I have a column in both tables called "Reporting Month".  When I add that to the x-axis, the percent jumps up to 112%.  I don't understand why adding the month column to the X axis would affect this. I've done several other line charts like this without any problems - but this is the first time I've had to do the calculation using columns from 2 different tables.  Am I missing something? The relationship between the two tables is based on the CI Identifier field - and it is a one to one. (The CI identifiers are all unique)

Alternatively, I do have the updated by dates in the "All CIs" table, but I couldn't figure out how to make it reflect a percentage updated for month to month. I created the bin's for the date in monthly separations, but I still can't get it to reflect just the percent of the total value, rather than the selected value (as soon as I select only January, it jumps to 100%). This issue seems to be a common issue I've not been able to properly solve.

Sorry for all the posts - I just have a quick approaching deadline for creating many reports.

[MalS · Accepted solution]

Are you able to post your *.pbix file, or a sample of the data? That would make it a little easier for me to understand.

A couple of questions:

What does the 'Reporting Month' represent in your tables?

How are your relationships set up? 

Do you have a Calendar table? 

Probably when you add month to the x axis, Power BI filters out all the rows that don't match that current month. So if you have 100 CIs in total, and 10 CIs in 'All CIs' with a Reporting Month of January, the count('All CIs'[CI Identifier]) formula will return 10 for the month of January. 

You might be able to use some measures like this:

All CIs = COUNTROWS(ALL('All CIs'[CI Identifier])

Updated CIs = COUNTROWS('Config Metrics - Updated'[CI Identifier])

% Updated = [Updated CIs] / [All CIs]

(The 'ALL' function tells Power BI to ignore any filters when computing 'ALL CIs')

[Lenihan]

I actually wasn't aware of a calendar table until now after reading this post and another post. I will investigate / search how to create them.

I'll give that formula a try too, thank you

Edit: That way with the formula did resolve the issue. thanks!