Desktop

Regular Visitor
Posts: 40
Registered: ‎01-31-2017

percentage grand total from a count distinct

hi all,

am having a small issue with a new measure i want to make and this is the case:

the report contains casenumbers (like 123456) which i transformed via count to a nr and next to the case nr i have a type (cancellation/delivery). At the end i have 2 columns:

count of caseNumbers     Type

100                                    delivery

50                                      cancellation

Now i want to have a third column (new measure) with the percentage of the grand total (=150). Example for type delivery the percentage is 67%.

any ideas how to do this especially as the case numbers needs to be counted....

M

Accepted Solutions
Moderator
Posts: 9,441
Registered: ‎03-06-2016

Re: percentage grand total from a count distinct

Hi @emveha,

Assume data likes below:

You can create a measure below:

Percentage = var t=SUMMARIZE(ALL(Table1),Table1[Type],"count",DISTINCTCOUNT(Table1[CaseNumber]))
return
DIVIDE(CALCULATE(DISTINCTCOUNT(Table1[CaseNumber]),ALLEXCEPT(Table1,'Table1'[Type])),SUMX(t,[count]))

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

All Replies
Moderator
Posts: 9,441
Registered: ‎03-06-2016

Re: percentage grand total from a count distinct

Hi @emveha,

Assume data likes below:

You can create a measure below:

Percentage = var t=SUMMARIZE(ALL(Table1),Table1[Type],"count",DISTINCTCOUNT(Table1[CaseNumber]))
return
DIVIDE(CALCULATE(DISTINCTCOUNT(Table1[CaseNumber]),ALLEXCEPT(Table1,'Table1'[Type])),SUMX(t,[count]))

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor
Posts: 40
Registered: ‎01-31-2017

Re: percentage grand total from a count distinct

hi Qiuyun Yu,

thanks for swift response and offering this solution, it works!!

M