I need percentages in a report, tried searching but nothing yet

[rde]

I have a data table

number, resolved,groupid,responsetime

I have one measure  countickets=COUNT([number])

it creates the report I need (see images) but now the users want % for each value

I hope I have given enough information.

my current reportexample of adding percentagesbasic data

[rde]

I think we are close, but I forgot to give you two pieces of information

one I have a calendar so I am using the calendar date. that was an easy fix

Percentage =

VAR _total =

CALCULATE (

DISTINCTCOUNT ( SnowMonthlyActivity[number]),

ALLEXCEPT ( D_CAL_V, D_CAL_V[CAL_DATE])

)

RETURN

DIVIDE ( DISTINCTCOUNT ( SnowMonthlyActivity[number]), _total, 0 )

But I forgot there is one more column and actually it is the MOST important column

Over30Days

so this data is tickets that are handled by our call center

and we identify each ticket as follows (in column Over30Days)

0-2

3-5

6-30

Over30

So I updated you formula as follows"

PercOver30 =

VAR _total =

CALCULATE (

DISTINCTCOUNT ( SnowMonthlyActivity[number]),

ALLEXCEPT ( D_CAL_V, D_CAL_V[CAL_DATE])

)

RETURN

DIVIDE ( DISTINCTCOUNT ( SnowMonthlyActivity[Over30days]), _total, 0 )

But that did not work.  

Not sure if this is enough information.  If not I will create data in a text file

Please let me know, thanks

[v-yiruan-msft]

Hi @rde ,

What's your expected result? Do you want to get the percentage of the number of tickets that are more than 30 days old in the total number of tickets or something else? Could you please provide the original data and the expected result with some examples or screenshot? By the way, column [Over30Days] is a fact column or calculated column? If it is a calculated column, please share its formula with me. Thank you.

Best Regards

[rde]

so yes ultimately the over30day/0-2/3-5/6-30 count as a percentage of total tickets for the month is the goal

so there is another column "opened" and the days difference between Opened and resolved is the over30day calculation.  Over30days is a calculated column (not a measure)

Over30days =

IF (DATEDIff(SnowMonthlyActivity[Opened],SnowMonthlyActivity[Closed]-2,DAY)<=2,"0-2",

IF (DATEDIFF(SnowMonthlyActivity[Opened],SnowMonthlyActivity [Closed]-2,DAY)<=5,"3-5",

IF (DATEDIFF(SnowMonthlyActivity[Opened],SnowMonthlyActivity[Closed]-2,DAY)<=30,"6-30",

IF (DATEDIFF(SnowMonthlyActivity[Opened],SnowMonthlyActivity[Closed]-2,DAY)>30,"Over30"))))

I have a txt file with March 2021 data, but do not know how to attach it. let me research that

[v-yiruan-msft]

Hi @rde ,

You can create a measure as below, please find the attachment for the details.

Percentage = 
VAR _total =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[number] ),
        ALLEXCEPT ( 'Table', 'Table'[resolved] )
    )
RETURN
    DIVIDE ( DISTINCTCOUNT ( 'Table'[number] ), _total, 0 )

If the above one is not working for your scenario, please provide some sample data in Text format (exclude sensitive data) and your expected result with calculation logic and more details. Thank you.

Best Regards

[vanessafvg]

can you provide sample data in a text format?

[rde]

I cannot figure out how to upload my text file.  I do not have any type of web space to create a "link"  Does this board allow uploads?  Am I missing something?

[rde]

one drive with Text file 

[v-yiruan-msft · Accepted solution]

Hi @rde ,

I updated the sample pbix file(see attachment), please check whether that is what you want.

Best Regards

[rde]

so i updated the formula.  I suspect my numbers are not working because I "summarize" the assignment groups.  so I have a table that "groups" all the "assigned to" into larger groups.  i.e. Randy Etheridge to L2Support etc.

when I plug in your formula I get this
as you can see 2,509/4,328 should be 57.97%

but the calculation is 0.739%

Assignment Group rollup