Ranking and distribution of points between the top three

[Divous]

Hi community,

I have a rating table with Producers and Products:

Producent	Product	Time	Bonus	Satisfaction
TechCom	QuantumX	4	3	5
NovaTech	StellarGlow	5	4	4
CosmoCorp	NebulaVibe	2	2	3
StarSys	AstroWave	2	1	2
HyperMega	CosmosShift	5	5	5
TechCom	NebulaVibe	3	2	4
NovaTech	QuantumX	4	3	5
CosmoCorp	AstroWave	2	1	2
StarSys	StellarGlow	5	4	3
HyperMega	NebulaVibe	3	2	4
TechCom	AstroWave	2	1	2
NovaTech	CosmosShift	5	5	5
CosmoCorp	StellarGlow	4	3	4
StarSys	CosmosShift	5	4	3
HyperMega	QuantumX	3	2	4

I need to create three separate tables that tell us the order of the top three Producers and divide the points of the other Producers proportionally between the top three. At the same time we will know how many points and what difference the top three Producers would have.

Desired output table (for Time):

Producent	Time	Time+	Time + Time+	Difference
NovaTech	14	6,42	20,42	45 %
StarSys	12	5,5	17,5	45 %
HyperMega	11	5,04	16,04	45 %
TechCom	9
CosmoCorp	8

I divide the values of the other Producers proportionally (one by one) between the first three (step by step for understanding):

TopThree =SUM(14+12+11) = 37

For TechCom:
Step 1 = 9/37 = 0,24

Step 2 =

14 * 0,24 = 3,4

12 * 0,24 = 2,91

11 * 0,24 = 2,67

For CosmoCorp:
Step 1 = 8/37 = 0,21

Step 2 =

14 * 0,21 = 3,02

12 * 0,21 = 2,59

11 * 0,21 = 2,37

Time+ (from table):

SUM Steps 2

etc and same for Bonus and Satisfaction columns. 

And last (4th) table is some kind of aggregation view where is Top 3 Producents with summed values of Time, Bonus and Satisfaction:

Producent	T+B+S (rating)	Time+  Bonus+  Satisfaction+	SUM	Difference
NovaTech	41	17,21	58,21	41,98 %
CosmoCorp	34	15,42	49,42	45,35 %
StarSys	31	12,12	43,12	39,10 %

All tables should be filterable by other DIM tables and I need to use the values that came out in other calculations.

Is it possible to do something like that? Can I ask you for help?

Thanks in advance

Divous

[v-yueyunzh-msft]

Hi , @Divous 

Thanks for your sample data and detailed steps to get your end result first!
But i am not surely understand how to get your last table :

But i can help you to get your first need , here are the steps you can refer to :
(1)My test is the same which you have provided.

(2)We can create measures like this:

Time + = var _t = ADDCOLUMNS(  ALLSELECTED('Table'[Producent]) , "Sum of Time" ,CALCULATE( SUM('Table'[Time])))
var _t2 =  ADDCOLUMNS(_t  ,"rank" , RANK(DENSE,_t,ORDERBY([Sum of Time],DESC ,[Producent] ,ASC)))
var _top3  = FILTER(_t2 , [rank] <=3)
var _TopThree = SUMX(_top3 , [Sum of Time])
var _not_top3_table = ADDCOLUMNS(  FILTER(_t2 , [rank] >3) , "percent" , DIVIDE( [Sum of Time] ,_TopThree))
var _total_percentage = SUMX(_not_top3_table, [percent])
VAR _cur_pro =  MAX('Table'[Producent])
var _cur_rank = MAXX( FILTER( _t2 , [Producent] = _cur_pro) , [rank])
return
IF( _cur_rank<=3 , _total_percentage * SUM('Table'[Time]))

Time+ Time+ = IF([Time +]=BLANK() , BLANK(), SUM('Table'[Time]) + [Time +])

Difference = DIVIDE( [Time +] , SUM('Table'[Time]))

Bonus + = var _t = ADDCOLUMNS(  ALLSELECTED('Table'[Producent]) , "Sum of Bonus" ,CALCULATE( SUM('Table'[Bonus])))
var _t2 =  ADDCOLUMNS(_t  ,"rank" , RANK(DENSE,_t,ORDERBY([Sum of Bonus],DESC ,[Producent] ,ASC)))
var _top3  = FILTER(_t2 , [rank] <=3)
var _TopThree = SUMX(_top3 , [Sum of Bonus])
var _not_top3_table = ADDCOLUMNS(  FILTER(_t2 , [rank] >3) , "percent" , DIVIDE( [Sum of Bonus] ,_TopThree))
var _total_percentage = SUMX(_not_top3_table, [percent])
VAR _cur_pro =  MAX('Table'[Producent])
var _cur_rank = MAXX( FILTER( _t2 , [Producent] = _cur_pro) , [rank])
return
IF( _cur_rank<=3 , _total_percentage * SUM('Table'[Bonus]))

Satisfaction + = var _t = ADDCOLUMNS(  ALLSELECTED('Table'[Producent]) , "Sum of Satisfaction" ,CALCULATE( SUM('Table'[Satisfaction])))
var _t2 =  ADDCOLUMNS(_t  ,"rank" , RANK(DENSE,_t,ORDERBY([Sum of Satisfaction],DESC ,[Producent] ,ASC)))
var _top3  = FILTER(_t2 , [rank] <=3)
var _TopThree = SUMX(_top3 , [Sum of Satisfaction])
var _not_top3_table = ADDCOLUMNS(  FILTER(_t2 , [rank] >3) , "percent" , DIVIDE( [Sum of Satisfaction] ,_TopThree))
var _total_percentage = SUMX(_not_top3_table, [percent])
VAR _cur_pro =  MAX('Table'[Producent])
var _cur_rank = MAXX( FILTER( _t2 , [Producent] = _cur_pro) , [rank])
return
IF( _cur_rank<=3 , _total_percentage * SUM('Table'[Satisfaction]))

(3)Then we can put this measure on the visual and we can get the result as follows:

You can also use this value to get other value you want , if my understand can not meet your need , you can detailed tell me the calculation for your end result so that we can help you better !

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Divous]

Hi, @v-yueyunzh-msft Aniya,

thank you very much for your help it works perfectly! You got it exactly right.

Let me explain 4th table:

Columns:
1) Just SUM of values from rating table (top 3 overall = Time+Bonus+Satisfaction)
2) Same principle like in separate tabels, but SUMed
3) SUM 1)+2)
4) Difference between 3) and 1)


In the meantime, we came up with another display that would be very useful. Can I ask you?

5th table:

Is based on the Time, Bonus and Satisfaction tables.

It is about creating an overall ranking of Producers. We have three tables (Time, Bonus, Satisfaction) and we know the order:

I add up each Producer's ranking and the one with the fewest "points" is the best:

So, NovaTech is first because in Time, Bonus, Satisfaction tables is first (1+1+1); HyperMega is second (3+2+2); StarSys is third (2+5+3) etc

Best Regards,

Divous

[v-yueyunzh-msft]

Hi, @Divous 

Thanks for your quick response! This is my understand for your question:

(1)Sure , you can get the sum of the TopThree Time+ value. You can use this dax code:

The total extra Time = SUMX( ADDCOLUMNS(ALLSELECTED('Table'[Producent]) , "Time+" , [Time +]) , [Time+])

(2)For your 5th table need , we can also create a measure like this:

Measure = var _t = ADDCOLUMNS(  ALLSELECTED('Table'[Producent]) , "Sum of Time" ,CALCULATE( SUM('Table'[Time])) ,"Sum of Bonus" ,CALCULATE( SUM('Table'[Bonus])) , "Sum of Satisfaction" ,CALCULATE( SUM('Table'[Satisfaction]))   )

var _t2 =  ADDCOLUMNS(_t  ,"rank_Time" , RANK(DENSE,_t,ORDERBY([Sum of Time],DESC ,[Producent] ,ASC)),

    "rank_Bonus" , RANK(DENSE,_t,ORDERBY([Sum of Bonus],DESC ,[Producent] ,ASC)),

    "rank_Satisfaction" , RANK(DENSE,_t,ORDERBY([Sum of Satisfaction],DESC ,[Producent] ,ASC))

    )

var _t3 = ADDCOLUMNS(_t2 , "Sum" , [rank_Time]+[rank_Bonus]+[rank_Satisfaction])

var _cur_pro =  MAX('Table'[Producent])

return

SUMX(FILTER(_t3 , [Producent] = _cur_pro) , [Sum])

Then we can get the result like this:

(3)For your 4th table , i still do not understand your end result , you said that the T+B+S is the sum of this column from the rating table , but when i put this in the visual , the value is different from this table:

T+B+S = SUM('Table'[Time])+SUM('Table'[Bonus])+SUM('Table'[Satisfaction])

In my understand the TopThree based on the [T+B+S] are {"NovaTech","HyperMega","StarSys"} , but in your end result is this:

So i may not surly understand how you get this table , and for "2) Same principle like in separate tabels, but SUMed" , dose it based on the [T+B+S]?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly