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.
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)
Download:
Grazie a tutti
Paol ·
Solved! Go to 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:
3) 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] )
)
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:
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 } )
)
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
Proud to be a Super User!
Paul on Linkedin.
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....
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
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:
3) 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] )
)
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:
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 } )
)
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
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |