Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Deriving user logging % based on Quarterly, Half Yearly and Yearly

‎08-27-2019 02:08 AM

Can you help me in creating a visualization on number of Users logged in Quarterly, Half Yearly and Yearly in percentage.

Example

Raw data consists of Quarter, Year, User and      Country

1:User Sam 10% of total users logged in Q12013 (3 months, Last 3 month)

2:User Yan 70% of total users logged in Q1 & Q2 2016 (6 months)

QuarterYearFirst NameCountry
32019abhilashIndia
22019abhilashIndia
32019abhilashIndia
32019abhilashIndia
32015abhilashIndia
42015abhilashIndia
42014abhilashIndia
22018abhilashIndia
32017abhilashIndia
32019abhilashIndia
42018abhilashIndia
12013abhilashIndia
22019abhilashIndia
22018abhilashIndia
32019abhilashIndia
32019abhilashIndia
22019abhilashIndia
32017abhilashIndia
32019abhilashIndia
12018abhilashIndia
32018abhilashIndia
32019abhilashIndia
22019abhilashIndia
12015abhilashIndia
42013abhilashIndia
32017abhilashIndia
22019abhilashIndia
22018abhilashIndia
32019abhilashIndia
22019abhilashIndia
32019abhilashIndia
32019abhilashIndia
32019abhilashIndia
32019ArijitIndia
32019arshadIndia
32019AseemIndia
32019ashvinIndia
32019asrujitIndia
22019AtulIndia
22019AtulIndia
22018AtulIndia
22019AtulIndia
32019AtulIndia
42018AtulIndia
32015AtulIndia
22019AtulIndia
32017AtulIndia
32019AtulIndia
32019AtulIndia
22019AtulIndia
22016AtulIndia
32019AtulIndia
22019AtulIndia
22019AtulIndia
42016AtulIndia
32019AtulIndia
42018AtulIndia
22018AtulIndia
32019AtulIndia
32019AtulIndia
12019AtulIndia
32019AtulIndia
22018AtulIndia
32019AtulIndia
22019AtulIndia
22018AtulIndia
32019AtulIndia
32019AtulIndia
22018AtulIndia
32019AtulIndia
32018AtulIndia
32019AtulIndia
32019AtulIndia
42016AtulIndia
32019AtulIndia
22018AtulIndia
22019AtulIndia
32019AtulIndia
42018GyanendraIndia
42017HAREKRISHNAIndia
42015harishIndia
32019harpreetIndia
32019HarryIndia
32019Har**bleep**aIndia
22019hemrajIndia
22019hiteshIndia
32019imranIndia
32017IrshadIndia
22019IrshadIndia
32019IrshadIndia
22019IrshadIndia
32019IrshadIndia
22018IrshadIndia
22017IrshadIndia
32019IrshadIndia
32019IrshadIndia
22019IrshadIndia
32019IrshadIndia
32017IrshadIndia
32019IrshadIndia
22018IrshadIndia
32019IrshadIndia
22018IrshadIndia
32019IrshadIndia
32019IrshadIndia
32016IrshadIndia
22019IrshadIndia
22018IrshadIndia
42018IrshadIndia
32019IrshadIndia
32019IrshadIndia
22017IrshadIndia
42018IrshadIndia
32019IrshadIndia
22018IrshadIndia
22018IrshadIndia
42018IrshadIndia
32019IrshadIndia
32017IrshadIndia
32019IrshadIndia
32015IrshadIndia
32019IrshadIndia
32019IrshadIndia
42015IrshadIndia
32019IrshadIndia
42016IrshadIndia
32018IrshadIndia
32019IrshadIndia
42016IrshadIndia
22018IrshadIndia
32019IrshadIndia
22018IrshadIndia
22019IrshadIndia
22019IrshadIndia
22019IrshadIndia
22019IrshadIndia
32019IrshadIndia
22019IrshadIndia
22019IrshadIndia
32019IrshadIndia
22017IrshadIndia
22018IrshadIndia
22019IrshadIndia
22019IrshadIndia
22018IrshadIndia
22018IrshadIndia
32017IrshadIndia
32019IrshadIndia
32019IrshadIndia
22019IrshadIndia
32019IrshadIndia
32016IrshadIndia
22019IrshadIndia
32019IrshadIndia
32018IrshadIndia
32019IrshadIndia
32019IrshadIndia
22019IrshadIndia
32019IrshadIndia
32019IrshadIndia
42016IrshadIndia
42016IrshadIndia
22019IrshadIndia
22019IrshadIndia
32019IrshadIndia
22018IrshadIndia
22018IrshadIndia
22018IrshadIndia
32016IrshadIndia
22018IrshadIndia
32019IrshadIndia
32017IrshadIndia
22018IrshadIndia
22019IrshadIndia
22019IrshadIndia
22019IrshadIndia
32019IrshadIndia
32019IrshadIndia
32019IrshadIndia
32019IrshadIndia
22019IrshadIndia
22015IrshadIndia
22016IrshadIndia
32018IrshadIndia
22018IrshadIndia
32019IrshadIndia
32019IrshadIndia
22019IrshadIndia
22019IrshadIndia
22018SANDEEPIndia
32017sandipIndia
22019SANJAYIndia
42018sanjivIndia
32015sanjivIndia
22019sanjivIndia
22019sanjivIndia
32019sanjivIndia
22019sanjivIndia
32019sanjivIndia
22019sanjivIndia
22019sanjivIndia
22019sanjivIndia
22019sanjivIndia
32019sanjivIndia
42016sanjivIndia
32019sanjivIndia
32019sanjivIndia
22018sanjivIndia
32019sanjivIndia
42016sanjivIndia
22018sanjivIndia
32019sanjivIndia
32017sanjivIndia
22019sanjivIndia
32019sanjivIndia
42018sanjivIndia
32019sanjivIndia
32019sanjivIndia
42018sanjivIndia
22019sanjivIndia
22018sanjivIndia
12019sanjivIndia
32019sanjivIndia
22018sanjivIndia
32019sanjivIndia
32019sanjivIndia
42016sanjivIndia
12019sanjivIndia
32019sanjivIndia
32019sanjivIndia
22019sanjivIndia
32019sanjivIndia
22019sanjivIndia
32018sanjivIndia
12014sanjivIndia
32019sanjivIndia
42016sanjivIndia
32017sanjivIndia
22019sanjivIndia
32019sanjivIndia
32019sanjivIndia
22019sanjivIndia
32019sanjivIndia
22019sanjivIndia
22016sanjivIndia
22019sanjivIndia
42015sanjivIndia
22019sanjivIndia
32015sanjivIndia
22018sanjivIndia
32019sanjivIndia
32019sanjivIndia
42015sanjivIndia
32019VimalIndia
32019VinayagamIndia
42016VIRAJIndia
32019VishnuIndia
12019VishwanathIndia
42018yasirIndia
32019YATINIndia
32019YOGESHIndia
22019Yogeshwara RaoIndia
32019YUVARAJIndia
22019ZAFARIndia

Thanks & Regards,

San

 

Labels:
Message 1 of 4
340 Views
0
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support
In response to Anonymous

‎08-29-2019 02:18 AM

Hi @Anonymous 

You could create a measure as below

Measure 2 =
IF (
    ISINSCOPE ( 'Table'[Year] ),
    IF (
        ISINSCOPE ( 'Table'[half year column] ),
        IF (
            ISINSCOPE ( 'Table'[Quarter] ),
            COUNT ( 'Table'[First Name] )
                / CALCULATE (
                    COUNT ( 'Table'[First Name] ),
                    ALLEXCEPT ( 'Table', 'Table'[Year], 'Table'[half year column] )
                ),
            COUNT ( 'Table'[First Name] )
                / CALCULATE (
                    COUNT ( 'Table'[First Name] ),
                    ALLEXCEPT ( 'Table', 'Table'[Year] )
                )
        ),
        COUNT ( 'Table'[First Name] )
            / CALCULATE ( COUNT ( 'Table'[First Name] ), ALL ( 'Table' ) )
    ),
    1
)

Capture4.JPGCapture5.JPGCapture6.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
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

Message 4 of 4
297 Views
0
3 REPLIES 3
v-juanli-msft
Community Support
Community Support

‎08-27-2019 08:25 PM

Hi @Anonymous 

I can only create number of Users based on Quarterly, Half Yearly and Yearly, but i don't know how to calculate the percentage.

which is the molecule?

 

Create a column

half year column = IF([Quarter]<=2,"first half","second half")

Add column "'first name" in the "Value" field, and select "count"

then you can expand the levels in a matrix visual.

Capture14.JPGCapture15.JPGCapture16.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 2 of 4
320 Views
0
Anonymous
Not applicable
In response to v-juanli-msft

‎08-28-2019 01:37 AM

Maggie,

 Am looking for deriving in percentage.

Message 3 of 4
311 Views
0
v-juanli-msft
Community Support
Community Support
In response to Anonymous

‎08-29-2019 02:18 AM

Hi @Anonymous 

You could create a measure as below

Measure 2 =
IF (
    ISINSCOPE ( 'Table'[Year] ),
    IF (
        ISINSCOPE ( 'Table'[half year column] ),
        IF (
            ISINSCOPE ( 'Table'[Quarter] ),
            COUNT ( 'Table'[First Name] )
                / CALCULATE (
                    COUNT ( 'Table'[First Name] ),
                    ALLEXCEPT ( 'Table', 'Table'[Year], 'Table'[half year column] )
                ),
            COUNT ( 'Table'[First Name] )
                / CALCULATE (
                    COUNT ( 'Table'[First Name] ),
                    ALLEXCEPT ( 'Table', 'Table'[Year] )
                )
        ),
        COUNT ( 'Table'[First Name] )
            / CALCULATE ( COUNT ( 'Table'[First Name] ), ALL ( 'Table' ) )
    ),
    1
)

Capture4.JPGCapture5.JPGCapture6.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 4 of 4
298 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All