Forum Discussion

Khalefa's avatar
Khalefa
Icon for Helper I rankHelper I
3 years ago
Solved

Iteration with multiple column conditions

Hi team Need support in the below a measure to calculate the overall attribute score on the review ID granularity considering the below:- 1- There are 6 attributes in each review id 2- The DAX sh...
Need Help DAX measure
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Khalefa 
    Please refer to attached amended sample file

    Weighted Score = 
SUMX (
    VALUES ( 'Fact'[Review ID] ),
    IF (
        0
            IN CALCULATETABLE (
                VALUES ( 'Fact'[Score] ),
                ALL ( Dim_Attribute[Attribute] ),
                Dim_Attribute[Attribute] IN { "Compliance", "Image" }
            ),
        0,
        SUMX (
            CALCULATETABLE ( 'Fact' ),
            'Fact'[Score] * RELATED ( Dim_Attribute[Weight] ) / 100
        )
    )
)
    Total Weight = 
SUMX (
    VALUES ( 'Fact'[Review ID] ),
    SUMX (
        CALCULATETABLE ( 'Fact' ),
        RELATED ( Dim_Attribute[Weight] ) 
    )
)
    % Score = 
DIVIDE (
    [Weighted Score],
    [Total Weight]
)
Khalefa's avatar
Khalefa
Icon for Helper I rankHelper I
3 years ago

tamerj1 
the results should be as follow

total weighted score (145 ) / total weight (400) 

Sumx output should be divided by total weight not just 100

Review IDSiteChannelAttributeScoreWeight Weighted score
1Site1CallValidation10015 15
1Site1CallCompliance1000 0
1Site1CallImage1000 0
1Site1CallKnowledge040 0
1Site1CallLanguage015 0
1Site1CallAccuracy10030 30
2Site1CallValidation10015 0
2Site1CallCompliance00 0
2Site1CallImage1000 0
2Site1CallKnowledge040 0
2Site1CallLanguage10015 0
2Site1CallAccuracy030 0
3Site2ChatValidation015 0
3Site2ChatCompliance00 0
3Site2ChatImage00 0
3Site2ChatKnowledge10040 0
3Site2ChatLanguage10015 0
3Site2ChatAccuracy030 0
4Site2ChatValidation10015 15
4Site2ChatCompliance1000 0
4Site2ChatImage1000 0
4Site2ChatKnowledge10040 40
4Site2ChatLanguage10015 15
4Site2ChatAccuracy10030 30
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Khalefa 

    145/400 = 36% and this is exactly the result obtained at the total level. Am I missing something?

    • Khalefa's avatar
      Khalefa
      Icon for Helper I rankHelper I
      3 years ago

      I just want to get the total score from your solution without percentage and another DAX for the total weight. this is what I meant.

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi , Khalefa 

        Thanks for your quick response and your sample .pbix file to us!

        Here are the steps you can refer to :
        (1)We can create a calculated column in 'Fact' Table:

        Weighted score = var _cur_ID = [Review ID]
var _t =SELECTCOLUMNS( FILTER('Fact' , 'Fact'[Review ID] = _cur_ID && 'Fact'[Attribute] in {"Compliance","Image"}) , "Score",[Score])
var _weight = RELATED('Dim_Attribute'[Weight])
var _cur_sore = [Score] 
return
IF( {0} in _t , 0 ,  IF(_cur_sore=0,0, _weight))

        (2)Then we can create these measures:

        Total Weight = SUMX( VALUES('Fact'), RELATED('Dim_Attribute'[Weight]))
        Total Weight 2 = SUMX( ALL('Fact'), RELATED('Dim_Attribute'[Weight]))
        Score % = SUM('Fact'[Weighted score]) / [Total Weight]

        (3)Then we can put the fieds we need on the visual and we can meet your need:

        If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

        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