cancel
Showing results 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

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.

Best,

Cam

1 ACCEPTED SOLUTION
Community Support

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

Helper V

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"

Helper V

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

Helper V

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

Resolver II

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

Helper V

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

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

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

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

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

Helper V

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

Resolver II

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)``````
Super User

@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

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.