Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
chonchar
Helper V
Helper V

Calculate % of entries by domain

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. 

 

chonchar_0-1697049424042.png

Best,

 

Cam

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

12 REPLIES 12
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

@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_0-1697054549562.png

 

@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 

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

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@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
Helper II
Helper II

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
Helper V
Helper V

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

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)

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors