Forum Discussion

HankScorpio2's avatar
HankScorpio2
Icon for Helper I rankHelper I
1 year ago
Solved

Sum and filter a table based on a condition in another table

hi    I am trying to create a total possible score for each evaluation. The total can be different for each evaluation due to N/a marking for some criteria.   I have this example data from my rep...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi

     

    Please follow below steps to achieve expected results.

     

    Step 1 :- 

    Unpivot section columns from QualityData Table using Power Query. so, that you will get all sections in one column.

     

    Step 2:-

    Then create relationship between both table using Section Column. one to Many Relationship

     

    Step3 :-

    You will get correct results for Actual Score without any context modification

     

    Step 4:

    For getting Possible Score, Use below formula.

    Possible Score =
            CALCULATE(
                        SUM(MarkReference[Marks]),
                        CROSSFILTER(MarkReference[Section],QualityTable[Section],Both)
            )
     

     

     
    Please accept solution, if it resolve your issue.
     
    Thanks & Regards
    Pravin Angane

     

     

     

     

saud968's avatar
saud968
Icon for Memorable Member rankMemorable Member
1 year ago

1. Data Preparation

Ensure Consistent Data Types: Make sure the Value column in QualityData and the Mark column in MarkReference are of the same data type (e.g., both numbers).
Clear Relationships: Define a clear relationship between the two tables. This relationship should be based on the Section column (assuming you have a Section column in both tables to link them).
2. DAX Formula

Possible Score =
CALCULATE(
SUM(MarkReference[Mark]),
FILTER(
MarkReference,
RELATED(QualityData[Value]) <> BLANK()
)
)

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!