Is there anyway to create a pivot table with all these calculated fields?-CodePudding

I have a table of Name, date (every day of the year), and hours. I am trying to create a pivot table to analyze the compliance of the data. By that I mean, I need to know for every year how many people are complainant with the 1800 limit (sum of all hours for the year) and how many are not. i created the required results via 4 tables see image . but because i am using so many table, i am unable to do any drilldown/drill up analyzes. Is there a way to combine all of this in one table?

CodePudding user response：

Add your Pivot Table to the Data Model and then create three new measures:

CompliantCheck :=
IF (
    ISFILTERED ( Table1[Name] ),
    IF ( SUM ( Table1[Hours] ) > 1800, "Non-Compliant", "Compliant" ),
    ""
)

CompliantCount :=
IF (
    [CompliantCheck] = "",
    "",
    0
          CALCULATE (
            DISTINCTCOUNT ( [Name] ),
            FILTER ( ALL ( Table1[Name] ), [CompliantCheck] = "Compliant" )
        )
)

NonCompliantCount :=
IF (
    [CompliantCheck] = "",
    "",
    0
          CALCULATE (
            DISTINCTCOUNT ( [Name] ),
            FILTER ( ALL ( Table1[Name] ), [CompliantCheck] = "Non-Compliant" )
        )
)

Obviously change the names of the table and fields in the above if necessary. Your Pivot Table should then comprise:

Rows: Name and Fiscal Year

Values: Sum of Hours, Compliant Check, CompliantCount, NonCompliantCount