Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Cognosben
Frequent Visitor

Calculation for Aggregated per Year, Month & Day %

Hi,

I have a sample data set underneath and is trying to get an aggregated average for September. When using a column chart the percentage is correct when I go down to each specific day. But when I drill up to the month the average percentage is wrong. When I go up to the monthly level it shows 7,37% instead of 10,33%. This is of course since PBI just averages the Abandon % per row level.

 

How can I create a calculation for Abandoned % so that PBI aggregates this correctly on both Yearly, Monthly and Daily level when drilling up and down in eg a Bar chart? 

 

 

DAY                                                      Total Calls           Abandoned Calls              Abandoned %

2017-09-22 00:00:00.000              80                                          4                              5,00 %

2017-09-21 00:00:00.000              420                                        56                           13,33 %

2017-09-20 00:00:00.000              510                                        78                           15,29 %

2017-09-19 00:00:00.000              458                                        94                           20,52 %

2017-09-18 00:00:00.000              219                                        9                              4,11 %

2017-09-15 00:00:00.000              207                                        13                           6,28 %

2017-09-14 00:00:00.000              251                                        25                           9,96 %

2017-09-13 00:00:00.000              235                                        14                           5,96 %

2017-09-12 00:00:00.000              199                                        11                           5,53 %

2017-09-11 00:00:00.000              158                                        23                           14,56 %

2017-09-08 00:00:00.000              100                                        9                              9,00 %

2017-09-07 00:00:00.000              83                                          0                              0,00 %

2017-09-06 00:00:00.000              106                                        4                              3,77 %

2017-09-05 00:00:00.000              94                                          3                              3,19 %

2017-09-04 00:00:00.000              136                                        2                              1,47 %

2017-09-01 00:00:00.000              83                                          0                              0,00 %

                                                       3339                                     345                         7,37 %

                                              

                               Correct Monthly %          10,33 %

                               Wrong PBI Monthly %   7,37 %

1 ACCEPTED SOLUTION
chethan
Resolver III
Resolver III

@Cognosben

 

Create an Abandoned % in Dax 

Below is the DAX formula

 

 

Abandoned % =
DIVIDE ( SUM ( Table1[Abandoned Calls] ), SUM ( Table1[Total Calls] ), 0 )

 

Snap.PNG

& find the sample file attached here

 

Regards,

Chetan K

View solution in original post

3 REPLIES 3
chethan
Resolver III
Resolver III

@Cognosben

 

Create an Abandoned % in Dax 

Below is the DAX formula

 

 

Abandoned % =
DIVIDE ( SUM ( Table1[Abandoned Calls] ), SUM ( Table1[Total Calls] ), 0 )

 

Snap.PNG

& find the sample file attached here

 

Regards,

Chetan K

It worked. Thank you @chethan

Thyago_Rezende
Resolver I
Resolver I

Hi,

 

Can you show the relationship and measures?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors