Forum Discussion

IF's avatar
IF
Post Prodigy
3 years ago
Solved

Data in different rows

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.

 

NameMonthTypeScore1Score2Score3
A7.2022initial 1Score1/Score2
A7.2022#24 Score1/Score3
A8.2022second 2Score1/Score4
A8.2022#34 Score1/Score5
B7.2022initial 1Score1/Score6
B7.2022#43 Score1/Score7
B8.2022last 3Score1/Score8
B8.2022#50 Score1/Score9

Thanks in advance.

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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.

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      IF
      Post Prodigy
      3 years ago

      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

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    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's avatar
      IF
      Post Prodigy
      3 years ago

      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. 

       

      NameMonthTypeScore1Score2Score3
      A7.2022initial 1Score1/Score2
      A7.2022#24 Score1/Score2
      A7.2022initial 2Score1/Score2
      A8.2022second 2Score1/Score2
      A8.2022#34 Score1/Score2
      B7.2022initial 1Score1/Score2
      B7.2022#43 Score1/Score2
      B8.2022initial 4Score1/Score2
      B8.2022second 2Score1/Score2
      B8.2022last 3Score1/Score2
      B8.2022#50 Score1/Score2

       

      • v-yadongf-msft's avatar
        v-yadongf-msft
        Community Support
        3 years ago

        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.

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    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's avatar
      IF
      Post Prodigy
      3 years ago

      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's avatar
        v-yadongf-msft
        Community Support
        3 years ago

        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.