Reply
Regular Visitor
Posts: 40
Registered: ‎01-31-2017
Accepted Solution

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....

thanks in advance

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: 

 

w1.PNG

 

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]))

 

w2.PNG

 

 

 

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.

View solution in original post

Attachment

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

Re: percentage grand total from a count distinct

Hi @emveha,

 

Assume data likes below: 

 

w1.PNG

 

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]))

 

w2.PNG

 

 

 

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.
Attachment
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