Calculate % of entries by domain

[chonchar]

Hello,

I have a table of domain names. I would like to create a table that shows the % that each domain is of the total. 

Could someone please help? Is it a new table or just a measure? What is the DAX? @parry2k if you're there!! Thank you all. 

Best,

Cam

[parry2k]

@chonchar try these measures:

Count Domain = COUNTROWS ( FILTER ( Domain, COALESCE ( Domain[Domain], "" ) <> "" ) )

% Domain = DIVIDE ( [Count Domain], CALCULATE ( [Count Domain], ALLSELECTED ( ) ) )

@MarceloVieira good solution but will not exclude blanks which I think is one of the requirements. Cheers!!

[chonchar]

@MarceloVieira @parry2k 

I just realized that the domain name is in the fact table. It is basically being assigned a domain through a relationship. Does that domain have to actually exist in the table that you're calucating? I don't think you can calculate, countrows, etc through a relationship, right? I tried both. @MarceloVieira gives me 0s all the way down. @parry2k I can't close the "coalesce"

[chonchar]

@parry2k @Erokor @MarceloVieira 

I was able to count the domains in the FACT table. I want to count them in the DIMENSION table (lookup table) as the values I want to count do not actually exist in the fact table. My apologies, I fogot to mention that. 

[chonchar]

@parry2k @Erokor @MarceloVieira 

The FACT table is called : Master Login. Within the Master Login Table, there is a column called Email Domain. 

I then created a table visualizatoin with columns from both the FACT and DIMENSION tables, one of which is the Email domain.

Can you count things through relationships? Or only as the exist in either the fact or dimension table?

Thank you all 

[Erokor]

Absolutely you can calculate across relationships, that's what they're for! If going from a Fact to a Dimension table you'll want to use RELATED() if going from a DIMENSION to a fact table, you'll want to use RELATEDTABLE() during an iteration.

In your example iterating the Dimension table 'E-mail" you'll want to COUNTROWS(RELATEDTABLE(Master Login))

[chonchar]

@Erokor I DMd you. I tried adding the "CALCULATETABLE" in your DAX, but I was unable to get it to work. 

When I do this: 

Count of Domains =

COUNTROWS(RELATEDTABLE(MasterLogin))

It only returns the total of the Domains in [MasterLogin]. 

[v-shex-msft · Accepted solution]

HI @chonchar,

Here is a measure formula with variable table that aggregate table records based on domain field values and extract the current domain as condition to look up and summary related records to calculate the percent:

formula =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        [Domain],
        "DCount", COUNTA ( Table2[Domain] )
    )
VAR currDomain =
    VALUES ( Table1[Domain] )
RETURN
    DIVIDE (
        SUMX ( FILTER ( summary, [Domain] IN currDomain ), [DCount] ),
        COUNTROWS ( ALLSELECTED ( Table1 ) )
    )

Regards,

Xiaoxin Sheng

[MarceloVieira]

@parry2kmade the complete solution and it should work.

Count Domain = COUNTROWS ( FILTER ( your_fact_table_name, COALESCE ( your_fact_table_name[Domain_column], "" ) <> "" ) )

% Domain = DIVIDE ( [Count Domain], CALCULATE ( [Count Domain], ALLSELECTED ( ) ) )

//I just tried to make it more detailed.

[MarceloVieira]

Hello chonchar. Its a measure and you could use a matrix visual, with domain on the row with the measure on the valor.

This could work.

domain_pct = 
var domain_rows = countrows( domain_table_name )
var all_domain_rows = 
  calculate(
     countrows( domain_table_name ),
     allselected( ) )
return
   divide( domain_rows, all_domain_rows )

//if there are blank rolls in your table, is it not better to just filter them in power query?? or you could use the filter pane to filter all power bi model where a specific column is not blank.

[chonchar]

Also, I need it to not count the blanks in the total. 

[Erokor]

Maybe give this a go to not count blanks:

domain_pct = 
var non_blank_domain_rows = 
    countrows(
        FILTER(
            domain_table_name,
            NOT(ISBLANK(domain_table_name[domain_column_name]))
        )
    )
var all_non_blank_domain_rows = 
    calculate(
        countrows(
            FILTER(
                domain_table_name,
                NOT(ISBLANK(domain_table_name[domain_column_name]))
            )
        ),
        allselected()
    )
return
    divide(non_blank_domain_rows, all_non_blank_domain_rows)

[parry2k]

@chonchar the solution is provided based on the information you provided, you should send the full details with the data model if it involves other stuff. Carefully read this before posting a question. it will SAVE everyone's time

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490