Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
I really need your expertise with PowerBI Measures.
I have two source tables Incidents [ID, Incident Date, ...] and Changes [ID, Change Date, ...] from which I want to calculate an incident per change rate on a monthly basis.
Therefore, I created two Measures to calculate both values as per different filters. So far so good:
Solved! Go to Solution.
Hi @Anonymous,
I think you are going to want to turn your model into a star schema with at least a date table and a team table
then you can create super simple measures like
Changes = COUNTROWS('Change Table')
Incidents = COUNTROWS('Incident Table')
Incident Per Change = DIVIDE([Incidents], [Changes])
which will provide the following results
The link below provides a pbix file with the tables and measures suggested.
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
@Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
@Greg_Deckler Thank you for your prompt reply! But I am sorry, I think I did not get it yet...
You're saying I need to calculate the Min or Max values (shouldn't matter as the counts are static for the month, right?) out of my Measures? And then use these values to calculate the required quotient?
@Anonymous Need sample data as text in a table and expected output from the sample data to be more specific.
@Greg_Deckler Sure, please see sample data below.
Incident Table
Incident ID | Incident Date | Team | Category |
10002 | 23.05.2020 | Team A | Storage |
10003 | 30.05.2020 | Team B | Network |
10004 | 30.05.2020 | Team A | Compute |
10005 | 01.06.2020 | Team B | Storage |
10006 | 23.06.2020 | Team C | Network |
10007 | 04.07.2020 | Team A | Storage |
10008 | 05.07.2020 | Team B | Storage |
10009 | 10.07.2020 | Team A | Network |
10010 | 07.07.2020 | Team B | Compute |
Change Table
Change ID | Change Date | Team | Change Type |
201 | 23.05.2020 | Team A | Emergency |
210 | 23.05.2020 | Team B | Standard |
202 | 30.05.2020 | Team B | Standard |
203 | 30.05.2020 | Team A | Standard |
211 | 30.05.2020 | Team A | Standard |
212 | 30.05.2020 | Team B | Standard |
204 | 01.06.2020 | Team A | Standard |
213 | 01.06.2020 | Team A | Standard |
205 | 23.06.2020 | Team B | Standard |
206 | 04.07.2020 | Team A | Standard |
207 | 05.07.2020 | Team B | Standard |
209 | 07.07.2020 | Team A | Standard |
208 | 10.07.2020 | Team C | Emergency |
I want to calculate / visualize the quotient (Incidents / Changes) per Team and month.
Example:
I am calculating the following counts using the measures above:
Team A:
- 2 Incidents, 3 Changes in May
- 2 Incidents, 2 Changes in July
Out of these numbers, I want to calculate the quotient Incidents per Changes (in this case 2/3=66,66% in May and 2/2=100% in July) and display it in a clustered bar chart with multiple teams per month.
Of course, I still want to be able to apply different filters (e.g. Change Type). Therefore, it must be somehow dynamically.
Thanks a million for your support!
Hi @Anonymous ,
First create a month column in both 2 tables;
Then create 2 slicer table as below:
Then create a measure as below:
Rate X =
var _ChangeCount = CALCULATE(COUNT('Change Table'[Change ID]),FILTER(ALL('Change Table'),'Change Table'[Team]=SELECTEDVALUE('Slicer Table2'[Team])&&'Change Table'[Month]=SELECTEDVALUE('Slicer Table1'[Month])))
var _IncidentCount =CALCULATE(COUNT('Incident Table'[Incident ID]),FILTER(ALL('Incident Table'),'Incident Table'[Team]=SELECTEDVALUE('Slicer Table2'[Team])&&'Incident Table'[Month]=SELECTEDVALUE('Slicer Table1'[Month])))
Return
DIVIDE(_IncidentCount,_ChangeCount)
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
If I have solved your issue,pls mark the reply as answered to close it!
Hi @Anonymous,
I think you are going to want to turn your model into a star schema with at least a date table and a team table
then you can create super simple measures like
Changes = COUNTROWS('Change Table')
Incidents = COUNTROWS('Incident Table')
Incident Per Change = DIVIDE([Incidents], [Changes])
which will provide the following results
The link below provides a pbix file with the tables and measures suggested.
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Thank you! It's often easier as expected. I used a separate "Date" table and linked both tables to the "Date" table. When using the separate dates instead of the original columns, I achieved the right aggregation. Thank you for your support!