cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaoloCorrado
Regular Visitor

Filter Fact Value Table by multiple cluster segmentation

Hello everyone
I have this problem:
to each negotiation I have to assign a "score", based on the value of negotiention (ex. 150) and the date of the negotiation;

if it is included in the cluster (from 500 to 2000 score 1.00 - July 01 2020), but the cluster is updated every 6 months and I must not lose the history.


I have to keep the history of the clusters by appending them, because
a negotiation of 1000 in the first half of 2021 is worth score = 1.00
a negotiation of 1000 in the second half of 2021 is worth score = 0.00
__________________________________________________________________________
If I did not have the cluster of the first semester all the negotiations would have as score = 1.00

IF I have only 1 cluster for all negotiation =
  VAR VALQUO = Fact_negotiation[Value negotiation]
  VAR VALCLUSTER = FILTER(CLUSTER_SME_CONV,
           AND(CLUSTER[from] <= VALQUO, CLUSTER[to] >= VALQUO))
  VAR Result= CALCULATE(DISTINCT( CLUSTER[Score]), VALCLUSTER)
return
  Result

________________________________________________________________

I am trying to create a calculated column that assigns the score to each negotiation, taking into account the value and date of the negotiation.

 

(Format Date DAY / MONTH / YEAR)

 

history of the clusters .JPG

Expected Score.JPG

 

Download:

Sample Data (Excel) 

 

Grazie a tutti

Paol ·

1 ACCEPTED SOLUTION

Thank you for the sample data. It really helps.

Ok, here is one way.

1) Create a Dimension table for the semesters with the function "New table" under Modeling in the ribbon using:

 

Dim Score Period = DISTINCT('Date Table'[Sem])

 

2) Now set up the model as follows:

Model.JPG3) In the Cluster table, add a new index column using:

 

Index =
VAR RNK =
    RANKX (
        FILTER ( ALL ( ClusterTable ), ClusterTable[HY] = EARLIER ( ClusterTable[HY] ) ),
        [From],
        ,
        DESC
    )
RETURN
    CALCULATE (
        RNK,
        ALLEXCEPT ( ClusterTable, ClusterTable[HY], ClusterTable[From] )
    )

 

 

ClusterTable.JPG

4) Create the following measures to calculate the score:

 

 

Sum Result = SUM(ClusterTable[From])
Negotiation Value = SUM(FactTable[Value Negotiention])
Factor = SUM(ClusterTable[Score])
Score =
IF (
    ISINSCOPE ( 'Dim Score Period'[Sem] ),
    IF (
        ISBLANK ( [Negotiation Value] ),
        BLANK (),
        SWITCH (
            TRUE (),
            [Negotiation Value]
                >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 1 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 1 } ),
            [Negotiation Value]
                >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 2 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 2 } ),
            [Negotiation Value]
                >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 3 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 3 } ),
            [Negotiation Value]
                >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 4 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 4 } ),
            CALCULATE ( [Factor], ClusterTable[Index] IN { 5 } )
        )
    )
)

 

Now create the visual using the Semester field from the Dimension Score Period, and the rest from the fact table & add the score measure to get:
Result.JPG

If you prefer to have the Score as a column in the fact table, you can use:

 

Score Column =
SWITCH (
    TRUE (),
    FactTable[Value Negotiention]
        >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 1 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 1 } ),
    FactTable[Value Negotiention]
        >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 2 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 2 } ),
    FactTable[Value Negotiention]
        >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 3 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 3 } ),
    FactTable[Value Negotiention]
        >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 4 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 4 } ),
    CALCULATE ( [Factor], ClusterTable[Index] IN { 5 } )
)

 

 

Score Column.JPG

 


Caveat: you will have to tweak the [Score] measure/column if you have more than 5 score values in a semester

I've attached a semaple PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
PaulDBrown
Super User
Super User

Please share sample data (not an image) which you can paste from Excel into your post,  or a PBIX file (which you can share the link via OneDrive, Deopbox....





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






HI, this here is the link (OneDrive), where there is an Excel file with two tables 1.Fact, 2.Cluster

I hope it works

Sample Date (Excel) 

 

 

 

Paolo

Thank you for the sample data. It really helps.

Ok, here is one way.

1) Create a Dimension table for the semesters with the function "New table" under Modeling in the ribbon using:

 

Dim Score Period = DISTINCT('Date Table'[Sem])

 

2) Now set up the model as follows:

Model.JPG3) In the Cluster table, add a new index column using:

 

Index =
VAR RNK =
    RANKX (
        FILTER ( ALL ( ClusterTable ), ClusterTable[HY] = EARLIER ( ClusterTable[HY] ) ),
        [From],
        ,
        DESC
    )
RETURN
    CALCULATE (
        RNK,
        ALLEXCEPT ( ClusterTable, ClusterTable[HY], ClusterTable[From] )
    )

 

 

ClusterTable.JPG

4) Create the following measures to calculate the score:

 

 

Sum Result = SUM(ClusterTable[From])
Negotiation Value = SUM(FactTable[Value Negotiention])
Factor = SUM(ClusterTable[Score])
Score =
IF (
    ISINSCOPE ( 'Dim Score Period'[Sem] ),
    IF (
        ISBLANK ( [Negotiation Value] ),
        BLANK (),
        SWITCH (
            TRUE (),
            [Negotiation Value]
                >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 1 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 1 } ),
            [Negotiation Value]
                >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 2 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 2 } ),
            [Negotiation Value]
                >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 3 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 3 } ),
            [Negotiation Value]
                >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 4 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 4 } ),
            CALCULATE ( [Factor], ClusterTable[Index] IN { 5 } )
        )
    )
)

 

Now create the visual using the Semester field from the Dimension Score Period, and the rest from the fact table & add the score measure to get:
Result.JPG

If you prefer to have the Score as a column in the fact table, you can use:

 

Score Column =
SWITCH (
    TRUE (),
    FactTable[Value Negotiention]
        >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 1 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 1 } ),
    FactTable[Value Negotiention]
        >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 2 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 2 } ),
    FactTable[Value Negotiention]
        >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 3 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 3 } ),
    FactTable[Value Negotiention]
        >= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 4 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 4 } ),
    CALCULATE ( [Factor], ClusterTable[Index] IN { 5 } )
)

 

 

Score Column.JPG

 


Caveat: you will have to tweak the [Score] measure/column if you have more than 5 score values in a semester

I've attached a semaple PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!