Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |