Forum Discussion

sovereignauto's avatar
sovereignauto
Helper III
5 years ago
Solved

Detailed summary Table

Good Morning,   So once again after some help!  I have the attached which is an example as orginal has 2 years of data and many more rows and columns (about 10k rows per year for claims and 150k...
Show and Tell
Tips and Tricks
  • MFelix's avatar
    MFelix
    5 years ago

    Hi sovereignauto ,

     

    Create a calendar table then add the following measure to your model:

    Number claims open =
COUNTROWS (
    FILTER (
        Claims,
        Claims[Date Opend] <= MAX ( 'Calendar'[Date] )
            && Claims[Date Closed ] >= MAX ( 'Calendar'[Date] )
    )
)
    PercentageOfNotes =
VAR tempTable =
    SELECTCOLUMNS (
        FILTER (
            Claims,
            Claims[Date Opend] <= MAX ( 'Calendar'[Date] )
                && Claims[Date Closed ] >= MAX ( 'Calendar'[Date] )
        ),
        "Claim", Claims[Ref]
    )
VAR Notes_Values =
    SUMMARIZE (
        FILTER (
            'Notes',
            Notes[Date] <= MAX ( 'Calendar'[Date] )
                && Notes[Date]
                    >= MAX ( 'Calendar'[Date] ) - 2
                && Notes[Ref] IN tempTable
        ),
        Notes[Ref]
    )
RETURN
    DIVIDE ( COUNTROWS ( Notes_Values ), [Number claims open] )

     

    See result below and in attach PBIX file.

     

sovereignauto's avatar
sovereignauto
Helper III
5 years ago

MFelix thank you so much it is a great start! 

Do you know how i would be able to "group by" Handler as well as date? so for each date how many files each handler had?

MFelix's avatar
MFelix
Super User
5 years ago

Hi sovereignauto ,

 

Not sure if I understand what you want to but in the example I send out you just need to add the Handler to the Column series:

 

In the way the measures are calculated you can use any column on your tables to give further detail.