Forum Discussion

kasiao's avatar
kasiao
Frequent Visitor
5 years ago

Two fact tables with different granularity

Hello,

 

I’m new to Power BI and I come here from the world of Qlik!

I have a report with two fact tables:

  1. Client incident data
  2. Monthly Client numbers (split into Existing clients who were there at the start of the month + New clients who joined that month)

My report initially analysed incident data, however I’d like to enrich it to include client counts, for some added context.

Because my client totals are monthly, I can easily have a line chart with incidents by month and number of clients by month. The difficult arises when I want to look at the data on a quarterly basis because I can’t just sum the clients as I would be double counting existing clients.

I believe what I need is to sum all clients at the start of the quarter (new + existing) and add to it new clients from the next two months of that quarter, i.e.

Month

Existing

New

Jan

10

3

Feb

8

4

Mar

11

2

 

Total Clients in Q1 = (10+3) + 4 + 2 = 19

Is this the most logical solution or should I be approaching it differently?

 

Many thanks for looking 🙂

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    kasiao ,  if we need add the data to first date . you can create date from month year

     

    Try a measure like

    measure =

    var _min = minx(allselected(Date), Date[Date])

    return 

    calculate(sum(Table[Existing]), filter(Date, Date[Date] =_min)) + sum(Table[New])

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • kasiao's avatar
      kasiao
      Frequent Visitor
      5 years ago

      Thank you I managed to get it working!

       

      I now want to have a chart with a drilldown feature where I start with Quarters and can drill into Months.

      How can I achieve that? When I try with this calculation, only Jan is accurate on the graph, for the remaining months - only sum(Table[New]) is displayed.

       

      Thanks!

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi kasiao ，

         

        Could you provide some sample data with expected output?

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!