cancel
Showing results for
Did you mean:
Highlighted
Member

Cross Table formulas not working

Hi!

Having some challenges again getting some dax basic formulas to do some proper calculations

The 3 tables relevant to this are :

1) All CIs

2) Config Metrics - Updated.

3) Data table (this is a calculated table created with a unique key created following these instructions - https://pbidax.wordpress.com/2016/06/03/connect-any-number-of-tables-together-via-a-common-column/

Every table (except Data table) has a column called "Reporting Month". I have January 2017 and February 2017 as the values.

I need to calculate the % of CIs updated in the month. So, take # updated per month, and divide by total # of CIs that month.

First is:

UpdatedTotal = counta('Config Metrics - Updated'[CI Identifier])

This is Total count of CIs updated in the month. This will be the numerator.

Second is:

TotalCIs = counta('All CIs'[CI Identifier])

This is total # of CIs available. This will be the denominator

Both of these give correct values.

This third one:

% Updated = [UpdatedTotal] / [TotalCIs]

Is not giving me a value that even makes sense once i add the Reporting Month on the Axis. I've tried adding the column Reporting Month from the "All CIs" table, and from the "Config Metrics - Updated".

When I select the Reporting month column from "All CIs" table, it only shows a value for January (and it is incorrect)

When I select the Reporting month column from "Config Metrics - Updated", it says Infinity.

I'm unsure what to do next so that it is correct.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

Re: Cross Table formulas not working

Hi @Lenihan,

Since you have the relationship between these tables, you can calculate based on date table.

`	% Updated =COUNTA('Config Metrics - Updated'[CI Identifier]) /CALCULATE(COUNTA('All CIs'[CI Identifier]),RELATED(Date[report Month]))`

in addition, you can also use current reporting month to calcualte.

```	% Updated =
var currMonth= max('Config Metrics - Updated'[Reporting Month])
return
COUNTAX(FILTER(ALL('Config Metrics - Updated'),'Config Metrics - Updated'[Reporting Month]=currMonth),[CI Identifier])
/
COUNTAX(FILTER(ALL('All CIs'),'All CIs'[Reporting Month]=currMonth),[CI Identifier])```

If above is not help can you share some sample file?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
2 REPLIES 2
Community Support Team

Re: Cross Table formulas not working

Hi @Lenihan,

Since you have the relationship between these tables, you can calculate based on date table.

`	% Updated =COUNTA('Config Metrics - Updated'[CI Identifier]) /CALCULATE(COUNTA('All CIs'[CI Identifier]),RELATED(Date[report Month]))`

in addition, you can also use current reporting month to calcualte.

```	% Updated =
var currMonth= max('Config Metrics - Updated'[Reporting Month])
return
COUNTAX(FILTER(ALL('Config Metrics - Updated'),'Config Metrics - Updated'[Reporting Month]=currMonth),[CI Identifier])
/
COUNTAX(FILTER(ALL('All CIs'),'All CIs'[Reporting Month]=currMonth),[CI Identifier])```

If above is not help can you share some sample file?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Member

Re: Cross Table formulas not working

Thank you @v-shex-msft   The second function worked (first I had some errors with the ,RELATED section, but first worked great, and gave all the correct values. Thank you!

When i read your formula, it's like "Of course!".. but.. i know I never would have gotten to that point. This may help me with other measures I have to create too.

Announcements

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Get your latest community news and announcements.

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 353 members 3,678 guests
Recent signins: