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,
I need help in finding out a way for measures and in applying the count column
I have two tables - One is Call Report and the Other Building Report
The call report has details of calls occured during each date for one year or more
The Building report is like a master report - which has details of Assests in each building.
Building report looks like below
Call Report looks like below that calculates number of calls
I wanted to find call rate using measures in PowerBi - Call Rate = No of Calls/No. of Units
When I put the data in PowerBi
When I calculate the Call Rate - the number of units changes each year, which shouldnt because the building has same assest throughout the year.
Below is the excel calculation :
Call Rate | Call Rate = No of Calls/No. of Units | |
Building Name | Jan-21 | Feb-21 |
Building 1 | 0.67 | 0.33 |
Building 2 | 0 | 1.2 |
Building 3 | 0.5 | 2.5 |
Solved! Go to Solution.
Sorry,
Atlast found it out where I was going wrong.
It was in data cardinality - many to many, which I figued it out now that created the filtering even after adding the date
Sorry,
Atlast found it out where I was going wrong.
It was in data cardinality - many to many, which I figued it out now that created the filtering even after adding the date
Hello @Anonymous
so you found a solution?
BR
Jimmy
Hello @Anonymous
and where is the question? What is the problem?😉
The measure has to first count rows divided distinct count of rows of your Unit ID.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
I know it would be confusing 😉
Could you let me know how to write the measure.
I have
Measure 1 which is
M_No.of Units = COUNT('Current/Lost Units - Merged'[Supervisor])
Measure 2
M_No.of Calls = COUNT('Call Report'[Call])
Measure 3
M_CallRate = DIVIDE([M_No.of Units],[M_No.of Calls])
But this doesnt give me the correct answer since each year the Measure 1 keeps changing each month, which shouldnt happen - If the building has 10 units, this should remain the same every year.
The last photo in my message does show it
Hello @Anonymous
what you mean the measure is changing every year? The datamodel will take care of that as soon you integrate a date. Meansing filtering your tables when you use a date and then the measure output changes accordingly. For me the first question is how you get the no of units the right way. Is this a distinct count of a column or a count of a table that contains x columns of your table? Depending on that you have to create a formula that does exactly that like SELCTCOLUMNS that gives you x columns of a table and then use the distinct count on that.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
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.