Forum Discussion
Value summation by date
Hello,
I have a table as follows:
| Date | Department | Received | Within 24 hrs | Within 48 hrs |
| 07-11-16 | Cornea | 7 | 2 | 3 |
| 07-11-16 | Glaucoma | 10 | 3 | 2 |
| 07-11-16 | Retina | 8 | 4 | 3 |
| 07-11-16 | Cataract | 8 | 1 | 2 |
| 14-11-16 | Cornea | 10 | 5 | 1 |
| 14-11-16 | Glaucoma | 10 | 3 | 3 |
| 14-11-16 | Retina | 7 | 4 | 1 |
| 14-11-16 | Cataract | 8 | 2 | 2 |
The need is to have % of requests resolved within 24 hrs and 48 hrs, by department and date. For example:
For date 07-11-16 and Cornea department, 29% requests are resolved within 24hrs (2 divided by 7) and 43% requests are resolved within 48hrs (3 divided by 7).
I have imported the table in PBI and have unpivoted the columns, which looks as follows:
I am also trying to create a column "Cornea24hrs" as seen in the figure, which is not successful.
Your help by way of detailed steps would be much appreciated.
Thanks a ton.
Hi deepvibha
Just change the measures as follows and rest remaining the same as my previous reply
Create Measures - SumReceived, Sum24Hrs, Sum48Hrs as follows
SumReceived = Calculate(sum (FactTable[Received]),FactTable[Attribute] = "Received")
Sum24Hrs = Calculate(sum (FactTable[Received]),FactTable[Attribute] = "Within 24 Hrs")
Sum48Hrs = Calculate(sum (FactTable[Received]),FactTable[Attribute] = "Within 48 Hrs")
This should work.
If this works please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
Hi deepvibha
You will not be ablt to expactly replicate the excel output in Power BI.
There are two approaches possible.
1. Create a column called DateByDepartment = Format([TransDate],"DD/MM/YYYY") & "-" & [Department]
2. Use this as a x-axis and then the measures 24hrs% and 48hrs% as Y-axis.
The out put will look like
The x-axis will look like
Very close to Excel
The second approach is to create a hierarchy
1. Right click on the Date column of your table and select NewHierarchy
2. Drag the Department also under this.
3. Rename this hierarchy as TransDateDepartment
4. Use this as x-axis and the measures as y-axis.
5. The chart will initially look like
6. Click on the weighing scale like icon you will see the output as
When you click on the two down arrows icon you will get it at Department level.
If this works give additonal KUDOS.
Cheers
CheenuSing
8 Replies
Hi deepvibha
Please try the following steps :
1. Create Measures - SumReceived, Sum24Hrs, Sum48Hrs as follows
SumReceived = Sum(FactTable[Received])
Sum24Hrs = sum (FactTable[Within 24 hrs])
Sum48Hrs = sum (FactTable[Within 48 hrs])
2. Now create the measure %24Hrs, %48Hrs
%24hrs = divide ([Sum24Hrs],[SumReceived])
%48hrs = divide ([Sum48Hrs],[SumReceived])
3. Make these two % measures as data type perccentage.
4. You should get what you wanted, see the screen shot.
If this works for you please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
