Data in different rows

[IF]

Hi,

I am using a data source that is not really designed in a good way. I have slicers for the month, type, and name. Score 3 is calculated by Score1/Score2. For example; Name=A, Month=7.2022 should provide the score 1/24. As you can see from the example data, they are not in the same row and I don't know how to make them one row or calculate Score3.

Name	Month	Type	Score1	Score2	Score3
A	7.2022	initial		1	Score1/Score2
A	7.2022	#	24		Score1/Score3
A	8.2022	second		2	Score1/Score4
A	8.2022	#	34		Score1/Score5
B	7.2022	initial		1	Score1/Score6
B	7.2022	#	43		Score1/Score7
B	8.2022	last		3	Score1/Score8
B	8.2022	#	50		Score1/Score9

Thanks in advance.

[v-yadongf-msft]

Hi @IF ,

OK, please create a measure:

New_score3 = IF(ISINSCOPE('Table'[Name]),MAX('Table'[score3]),SUM('Table'[Score2]) &"/"& SUM('Table'[Score1]))

Best regards,

Yadong Fang

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

[IF]

I really appreciate for your help and time. This time date and name is working but the "type" slicer is not bringing the result. regards,

[v-yadongf-msft · Accepted solution]

Hi @IF ,

I think the easiest fix is to also check the "#" in the slicer as shown in the image below:

Best regards,

Yadong Fang

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

[v-yadongf-msft]

Hi @IF ,

Please create an index column in Power Query:

Please try following DAX to create new columns:

New score1 = 
var cur_index = 'Table'[Index]
var pre_index = 'Table'[Index] + 1
var pre_value = CALCULATE(
    MAX('Table'[Score1]),
    FILTER('Table','Table'[Index] = pre_index)
)
return 
IF('Table'[Score1] = BLANK(),pre_value,'Table'[Score1])


New score2 = 
var cur_index = 'Table'[Index]
var pre_index = 'Table'[Index] - 1
var pre_value = CALCULATE(
    MAX('Table'[Score2]),
    FILTER('Table','Table'[Index] = pre_index)
)
return 
IF('Table'[Score2] = BLANK(),pre_value,'Table'[Score2])

Score3 = 'Table'[New score2] &"/"& 'Table'[New score1]

You will get result you want:

Best regards,

Yadong Fang

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

[IF]

Hi,

Thanks for the reply. I tried and noticed that I have some cases with more than 2 types. I added 3 more rows to the sample. For example, Name=B, date=8.2022 has initial (4), second (2) and last (3). In this case the score should be 4/50 for initial type, 2/50 for the second type and 3/50 for the last type. Most of the data has only one type, but some of them having 2 or 3 types. Thanks again. 

Name	Month	Type	Score1	Score2	Score3
A	7.2022	initial		1	Score1/Score2
A	7.2022	#	24		Score1/Score2
A	7.2022	initial		2	Score1/Score2
A	8.2022	second		2	Score1/Score2
A	8.2022	#	34		Score1/Score2
B	7.2022	initial		1	Score1/Score2
B	7.2022	#	43		Score1/Score2
B	8.2022	initial		4	Score1/Score2
B	8.2022	second		2	Score1/Score2
B	8.2022	last		3	Score1/Score2
B	8.2022	#	50		Score1/Score2

[v-yadongf-msft]

Hi @IF ,

Please try following DAX:

score3 = 
var cur_score1 = CALCULATE(MAX('Table'[Score1]),FILTER('Table','Table'[Type] = "#" && 'Table'[Month] = EARLIER('Table'[Month]) && 'Table'[Name] = EARLIER('Table'[Name])))
VAR newscore1 = IF('Table'[Score1] = BLANK(),cur_score1)
RETURN IF(newscore1 <> BLANK(),'Table'[Score2] &"/" & newscore1)

I think this is the result you want:

Best regards,

Yadong Fang

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

[IF]

Thank you very much, it is very closed. I provided two screenshots. If i don't select the month and type the result should be 10/93 (1+4+3+2)/43+50. Similarly if I select the month it should be 9 / 50. Regards.

[amitchandak]

@IF , Create a measure like

Divide(Sum(Table[Score1]), Sum(Table[Score2]))

And in visual-only use name and month, do use type 

else try

calculate(Divide(Sum(Table[Score1]), Sum(Table[Score2])) , filter(Allselected(Table), Table[name] = max(Table[name])  && Table[Month] = max(Table[Month])  ) )

[IF]

Hi,

I tried both options but doesn't work. I want to highlight again that I am using "type" slicer and the data is not normal. Thanks