cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rlee1982 Regular Visitor
Regular Visitor

Percentage of distinguish counts per category

Dear all

 

I am trying to create a table to caculate the percetage of each number by category among the sum of all categories (here is Day 1, 2, and 3)

 

Below is an example data set and expect result:

 

table1.JPG

 

The result I hope to return is in red, which is different from Power BI's default % of grand total.

expect_resultJPG.JPG

In Day 1, the 25% of expect return comes from 3 / (3+4+5)

 

Yet, in Power BI's default, since there are 7 distingush values within those three days, in Day 1, the % of grand total is 3 / 7 = 43%, which is not I need.

 

Please kindly let me know how I should write the formula. Thank you so much! 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Percentage of distinguish counts per category

No, a many to one shouldn't matter for this.  How are you getting the [daily visitors] into your view?  Try it with these measures (adjusted slightly).

 

daily visitor = DISTINCTCOUNT( Visitors[Visitor_Name] )
% of total daily visitor = 
DIVIDE (
    [daily visitor],
    CALCULATE (
        SUMX ( 
            VALUES ( Visitors[Day] ), 
            [daily visitor] 
        ),
        ALLSELECTED ( Visitors )
    )
)

View solution in original post

Super User
Super User

Re: Percentage of distinguish counts per category

Yes, you are close on the SUMX.

The X iterators (SUMX, MAXX, AVERAGEX, etc) say do a thing over a table of values. The X is the table, the part before the X is the thing it does with the table, the part in the middle is the values it does the thing to. So our formula does three things.

  1. Build a table of all unique entries in the Day column.
  2. Add the value of the [Daily Visitors] measure.
  3. Sum the amount in the Daily Visitors column.

sumx.jpg

The reason for the ALLSELECTED is so the SUMX for each row ignores the specific day that is on that row and just does the calculation for all the days in the visual.  If we didn't include that the denominator would be the same as the numerator because it would keep the filter on Day:

sumx table.jpg

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Percentage of distinguish counts per category

Hello @rlee1982 

Give these a try.

# of distinct visitors = DISTINCTCOUNT( Visitors[Visitor_Name] )
% of total daily visitor = 
DIVIDE (
    [# of distinct visitors],
    CALCULATE (
        SUMX ( 
            VALUES ( Visitors[Day] ), 
            [# of distinct visitors] 
        ),
        ALLSELECTED ( Visitors[Day] )
    )
)

percentofvisitors.jpg

rlee1982 Regular Visitor
Regular Visitor

Re: Percentage of distinguish counts per category

Thanks for your quick reply.

 

The result of your code in my side ends up like this chart below:

 

endup.JPG

 

I assume that might be because of the one-to-many relationship.

 

Customer_info

sample_2.JPG

 

Order_info

 

   sample_1.JPG

 

Would you mind to adjust the code you shared earlier a little bit? 

 

Thanks again!

Super User
Super User

Re: Percentage of distinguish counts per category

No, a many to one shouldn't matter for this.  How are you getting the [daily visitors] into your view?  Try it with these measures (adjusted slightly).

 

daily visitor = DISTINCTCOUNT( Visitors[Visitor_Name] )
% of total daily visitor = 
DIVIDE (
    [daily visitor],
    CALCULATE (
        SUMX ( 
            VALUES ( Visitors[Day] ), 
            [daily visitor] 
        ),
        ALLSELECTED ( Visitors )
    )
)

View solution in original post

rlee1982 Regular Visitor
Regular Visitor

Re: Percentage of distinguish counts per category

Is it a must to create a seperate new measure (like "daily visitor") here?

 

 

 

 

Super User
Super User

Re: Percentage of distinguish counts per category

You should, yes.  If you just pull the column from the table into the visual and set it to distinct count that only works in the 1 visual.  If you write a measure you can use it everywhere.

rlee1982 Regular Visitor
Regular Visitor

Re: Percentage of distinguish counts per category

Is my understanding of SUMX correct here?

 

Also, how does ALLSELECTED work here?

 

Many thanks!

 

question.JPG

 

 

 

Super User
Super User

Re: Percentage of distinguish counts per category

Yes, you are close on the SUMX.

The X iterators (SUMX, MAXX, AVERAGEX, etc) say do a thing over a table of values. The X is the table, the part before the X is the thing it does with the table, the part in the middle is the values it does the thing to. So our formula does three things.

  1. Build a table of all unique entries in the Day column.
  2. Add the value of the [Daily Visitors] measure.
  3. Sum the amount in the Daily Visitors column.

sumx.jpg

The reason for the ALLSELECTED is so the SUMX for each row ignores the specific day that is on that row and just does the calculation for all the days in the visual.  If we didn't include that the denominator would be the same as the numerator because it would keep the filter on Day:

sumx table.jpg

View solution in original post

rlee1982 Regular Visitor
Regular Visitor

Re: Percentage of distinguish counts per category

Thank you so much for this detailed explanation! Smiley Happy


@jdbuchanan71 wrote:

Yes, you are close on the SUMX.

The X iterators (SUMX, MAXX, AVERAGEX, etc) say do a thing over a table of values. The X is the table, the part before the X is the thing it does with the table, the part in the middle is the values it does the thing to. So our formula does three things.

  1. Build a table of all unique entries in the Day column.
  2. Add the value of the [Daily Visitors] measure.
  3. Sum the amount in the Daily Visitors column.

sumx.jpg

The reason for the ALLSELECTED is so the SUMX for each row ignores the specific day that is on that row and just does the calculation for all the days in the visual.  If we didn't include that the denominator would be the same as the numerator because it would keep the filter on Day:

sumx table.jpg


 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 311 members 2,831 guests
Please welcome our newest community members: