cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
skondi
Helper II
Helper II

How to Rank and get list of user for making segments dyanamic

Hi , I am trying to make the user list dyanamic based on their frequency.

 

Screenshot What I trying to get:

skondi_0-1603465246960.png

 

In above image my ranking function is not working 

ie 

 

FreqeuncyRanking = 
	Var r = RANKX(Fact_GA_DATA,[% GT_Frequency],,DESC,Skip)
return r

 

 

My Frequency_GT% is like :

 

% GT_Frequency = 
VAR CurrentFrequency = [M_Frequency]
VAR TotalFrequency =
	CALCULATE(
		[M_Frequency],
		REMOVEFILTERS(Fact_GA_DATA)
    )
RETURN 
    DIVIDE(
		CurrentFrequency,
		TotalFrequency
	)

 

 Let me know how can I make those segments dyanamic?

Thanks

6 REPLIES 6
Eyelyn9
Community Support
Community Support

Hi @skondi ,

Sorry for my misunderstand... You could use SUMX() instead of SUM( )

SUMX('Fact_GA_DATA',[% GT_Frequency measure])

If you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business. Thank you

 

Best Regards,
Eyelyn Qin

Hi @Eyelyn9 , Many thanks for your help, but I am still facing some basic issues.

Part-1 :

I am trying to extract the userID(MUID) but everything is coming under last seg-D as the _sum is 1 for all the MUID.

ExtractSegments(Measure) :

VAR _sum =
    CALCULATE (
        SUMX(Fact_GA_DATA,[% GT_Frequency] ),
        FILTER (
            ALL ( 'Fact_GA_DATA' ),
            [FreqeuncyRanking] <= MAXX(Fact_GA_DATA,[FreqeuncyRanking] )
        )
    )
RETURN
    IF (
        _sum <= 0.25,
        "Seg-A",
        IF ( _sum < 0.5, "Seg-B", IF ( _sum < 0.75, "Seg-C", "Seg-D" ) )
    )

Screenshot:

skondi_0-1604065462289.png

----------------------------------------------------------------------------------------------------------

Part-2: How can I make Segmentations, based on Custom columns? or by using Measures?

As I have large dataset(16M rows) per year, Currently I am trying to do it on small data sample. can you guide me on how to do this task proplery. my task is to extract the users per segmentation for the selected timeframe.

Link for sample data : SegmentationWorkOct2020.pbix

https://drive.google.com/drive/folders/1fVrKuN044YP1YoBVPf7goB8xcFHW8Omo?usp=sharing

Eyelyn9
Community Support
Community Support

Hi @skondi ,

According to my understand, you want to set a flag :Seg-A, Seg-B, Seg-C, Seg-D for the Top X of %GT_Frequency when it is less than 25%,50%,75%,100%, right?

You could use the following formula:

Measure =
VAR _sum =
    CALCULATE (
        SUM ( Fact_GA_DATA[% GT_Frequency] ),
        FILTER (
            ALL ( 'Fact_GA_DATA' ),
            'Fact_GA_DATA'[FreqeuncyRanking] <= MAX ( 'Fact_GA_DATA'[FreqeuncyRanking] )
        )
    )
RETURN
    IF (
        _sum <= 0.25,
        "Seg-A",
        IF ( _sum < 0.5, "Seg-B", IF ( _sum < 0.75, "Seg-C", "Seg-D" ) )
    )

My visualization looks like this:

10.27.4.1.PNG

Here is the pbix file.


Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

 

Best Regards,
Eyelyn Qin

 

@Eyelyn9 , Thanks for helping me, but I couldn't use your logic as it can't work on measure for this part.

SUM ( Fact_GA_DATA[% GT_Frequency] )

Fact_GA_DATA[% GT_Frequency] is a Measure ,its not a column. so sum is not working here.

My Measure formula is :

% GT_Frequency = 
VAR CurrentFrequency = [M_Frequency]
VAR TotalFrequency =
    CALCULATE(
        [M_Frequency],
        REMOVEFILTERS(Fact_GA_DATA)
    )
RETURN 
    DIVIDE(
        CurrentFrequency,
        TotalFrequency
    )

Can you help me here?

amitchandak
Super User IV
Super User IV

@skondi , Try like

FreqeuncyRanking =
return RANKX(allselected(Fact_GA_DATA[MUID]),[% GT_Frequency],,DESC,Skip)

or

FreqeuncyRanking =
return RANKX(allselected(Fact_GA_DATA[MUID]),[% GT_Frequency],,DESC,Skip) +rand()/100

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks alot @amitchandak , can you explain me how to group users for the 1st 25% of ' %GT_Frequency'

Steps can be :

1. I want to sum(%GT_Frequency) till it reaches 25% of the total Frequency.

2. Select above users and assign them a Segment name like Seg-A

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.