Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |