Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Solved! Go to Solution.
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.
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
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.
\
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
)
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.
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.
\
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
)
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
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
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.
Awesome, it worked. I thought of this method but wasn't sure how to translate it in a powerbi language
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |