Percentage of distinguish counts per category

[Anonymous]

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:

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

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! 

[jdbuchanan71 · Accepted solution]

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

[jdbuchanan71]

Hello @Anonymous 

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

[Anonymous]

Thanks for your quick reply.

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

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

Customer_info

Order_info

   

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

Thanks again!

[jdbuchanan71 · Accepted solution]

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

[Anonymous]

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

[jdbuchanan71]

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.

[Anonymous]

Is my understanding of SUMX correct here?

Also, how does ALLSELECTED work here?

Many thanks!

[jdbuchanan71 · Accepted solution]

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.

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:

[Anonymous]

Thank you so much for this detailed explanation! 

---

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

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:

---