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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Lenihan
Helper III
Helper III

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

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.

 

1 ACCEPTED SOLUTION
MalS
Resolver III
Resolver III

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')

 

 

 

View solution in original post

2 REPLIES 2
MalS
Resolver III
Resolver III

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')

 

 

 

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!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors