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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.