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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jacobson00
Regular Visitor

COUNT and Do a percentage

Hello all,

I searched around but couldn't figure this one out. I am new to powerbi.

 

I have a table with geographic information, Home and business and each person has a single id (called Entity ID). So each person will have two rows, one with the home info and one with business. For each address there is a column that flag if address is domestic.

I was trying to get the % of entity Id with a domestic address (from the whole list)

 

percentage =
DIVIDE (
COUNTROWS ( FILTER ( ALLSELECTED ( POWERBI_GEO_TBL ), POWERBI_GEO_TBL [IS_DOMESTIC] = "Y" ) ),
COUNTROWS ( ALLSELECTED ( POWERBI_GEO_TBL ) )
)
 
or  just get the distinct count
calculate = DISTINCTCOUNT(POWERBI_GEO_TBL[ENTITY_ID]); FILTER(POWERBI_GEO_TBL, POWERBI_GEO_TBL[IS_DOMESTIC] = "Y")
 
none worked. If i can get a mesure that gives me a cnt of those entity ids with a Y, i can create a card that will divide that number with the total distinct entity id
3 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

Try this measure

=CALCULATE(DISTINCTCOUNT(POWERBI_GEO_TBL[ENTITY_ID]),POWERBI_GEO_TBL[IS_DOMESTIC] = "Y")/DISTINCTCOUNT(POWERBI_GEO_TBL[ENTITY_ID])

Express this as a % age.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

amitchandak
Super User
Super User

Try

divide(
 calculate( DISTINCTCOUNT(POWERBI_GEO_TBL[ENTITY_ID]); FILTER(POWERBI_GEO_TBL, POWERBI_GEO_TBL[IS_DOMESTIC] = "Y")),
 calculate( DISTINCTCOUNT(POWERBI_GEO_TBL[ENTITY_ID]); ALL(POWERBI_GEO_TBL)))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

View solution in original post

v-lionel-msft
Community Support
Community Support

Hi @jacobson00 ,

I created two measures, you can try them, maybe one of them can help you.
If not, please give me a sample data model and specifically describe what you want to calculate.

y1.PNG\

 

Measure = 
VAR x = 
COUNTROWS(
    FILTER(
        Sheet8,
        [IS_DOMESTIC] = "Y"
    )
)
VAR y =
COUNTROWS(Sheet8)
RETURN
DIVIDE(
    x,y
)
Measure 2 = 
VAR x=
CALCULATE(
    DISTINCTCOUNT(Sheet8[Entity ID]),
    FILTER(
        Sheet8,
        [IS_DOMESTIC] = "Y"
    )
)
VAR y=
CALCULATE(
    DISTINCTCOUNT(Sheet8[Entity ID]),
    ALL(Sheet8)
)
RETURN
DIVIDE(
    x,y
)

 

y2.PNG

 

Best regards,
Lionel Chen

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-lionel-msft
Community Support
Community Support

Hi @jacobson00 ,

I created two measures, you can try them, maybe one of them can help you.
If not, please give me a sample data model and specifically describe what you want to calculate.

y1.PNG\

 

Measure = 
VAR x = 
COUNTROWS(
    FILTER(
        Sheet8,
        [IS_DOMESTIC] = "Y"
    )
)
VAR y =
COUNTROWS(Sheet8)
RETURN
DIVIDE(
    x,y
)
Measure 2 = 
VAR x=
CALCULATE(
    DISTINCTCOUNT(Sheet8[Entity ID]),
    FILTER(
        Sheet8,
        [IS_DOMESTIC] = "Y"
    )
)
VAR y=
CALCULATE(
    DISTINCTCOUNT(Sheet8[Entity ID]),
    ALL(Sheet8)
)
RETURN
DIVIDE(
    x,y
)

 

y2.PNG

 

Best regards,
Lionel Chen

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

It also did the trick

amitchandak
Super User
Super User

Try

divide(
 calculate( DISTINCTCOUNT(POWERBI_GEO_TBL[ENTITY_ID]); FILTER(POWERBI_GEO_TBL, POWERBI_GEO_TBL[IS_DOMESTIC] = "Y")),
 calculate( DISTINCTCOUNT(POWERBI_GEO_TBL[ENTITY_ID]); ALL(POWERBI_GEO_TBL)))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Ashish_Mathur
Super User
Super User

Hi,

Try this measure

=CALCULATE(DISTINCTCOUNT(POWERBI_GEO_TBL[ENTITY_ID]),POWERBI_GEO_TBL[IS_DOMESTIC] = "Y")/DISTINCTCOUNT(POWERBI_GEO_TBL[ENTITY_ID])

Express this as a % age.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Awesome, it worked. I thought of this method but wasn't sure how to translate it in a powerbi language

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.