Total percentage on 100%

[jorismo]

Hi all,

I'm currently creating an dashboard for our call statistics.

But there is an problem with it:

- When it's filtered on year or month the % is more than 100%, this can't be correct because you can't pick up more than 100% of the calls.

I really don't know how to figure it out to get those on a scale of 100%..

In attachment it the file.

Also the date format can't be changed to dd/mm/yyyy

https://drive.google.com/open?id=1jFCpiVeXVj-a61r3HBrDcKPjJpfB-nO-

[v-xjiin-msft]

Hi @jorismo,

In your scenario, the %answ or %SLA int or other percent values are the common columns in your source table. And when you put them in Values of a Matrix visual, they will be aggregated automatically. Like:

As parry suggested, it is better to use DAX expression to calculate them inside Power BI. Then comes the question, what's the logic to calculate those percentage values? Without knowing the logic, we cannot help a lot.

Thanks,
Xi Jin.

[jorismo]

well the dataset is an table where per day and customer an row is entered.

Every row has the number of calls answered and abandoned for that day.

I have to report the total % answered and abandoned per month, but if they use the drill-through also per day (if possible otherwise only per month).

[v-xjiin-msft]

Hi @jorismo,

The easiest way to achieve your requirement is to split your TimeKey to year and month columns with DAX expressions like:

Year = YEAR(Calls[TIME KEY]) and Month = MONTH(Calls[TIME KEY])

Then create a calculate table to summarize the values. I only put Calls Answered and Calls Abandoned in my sample:

Table =
SUMMARIZE (
    ALLSELECTED ( Calls ),
    [Teamoverview],
    Calls[Year],
    Calls[Month],
    "SUM Abandoned", SUM ( Calls[Calls Abandoned] ),
    "SUM Answered", SUM ( Calls[Calls Answered] )
)

Then create a new measure in this calculated table to calculate the % answered.

Ans % =
IF (
    MAX ( 'Table'[SUM Abandoned] ) + MAX ( 'Table'[SUM Answered] )
        > 0,
    DIVIDE (
        MAX ( 'Table'[SUM Answered] ),
        ( MAX ( 'Table'[SUM Abandoned] ) + MAX ( 'Table'[SUM Answered] ) )
    ),
    0
)

 

Thanks,
Xi Jin.

[jorismo]

Hi Xi Jin,

many many thanks for your help.

Thats the thing that I wanted!!!!! I'm going to creat this also for the abandoned calls etc.

But one thing isn't working ok, I also get all the months of 2018 (with the values of 2017)

 

[v-xjiin-msft]

Hi @jorismo,

Could you please share us the new pbix file if possible?

Thanks,
Xi Jin.

[parry2k]

Share sample data in excel file or share pbix file and ill get you the solution.

[parry2k]

You have to calculate the % within PowerBI using DAX. What I noticed that your % is pre calculated and it is doing the sum on % and that is why you are seeing more than 100%. Develop % calculation using DAX and that will do it for you.

[jorismo]

ok thanks for that, can you also give me an helping hand on the dax?

[parry2k]

no problem. so that would be the calculation to calculate %