DAX summary of data across multiple tables

[jakubalbrecht]

Hello,

zip file containg PBIX file (without authentication string for the data connected) can be downloaded here: https://www.vcas-os.cz/wp-content/uploads/2024/03/Dashboard-no-auth.zip

I am trying to create following dashboard showing the 4 metrics - each is presented as 5 weeks long timeline (from W-1 to W-5 reffered as "focused weeks") as well as a average value calcutated from those 5 weeks. Each monday the row is automatically shifted (W-1 becomes W-2 etc.)

Each week value is the sum of detailed records aggregated based on date belonging to the specific week. There are other columns in the detailed rows table which are used to filter only record relevant for the metrics calculated. The specific column is the Owner which is then used to slice the dashboard.

I am struggling with creating the 5-weeks average. I created following DAX code which works fine to show figures for all owner - with no owner selected in the slicer:

Metrics = 
ADDCOLUMNS(
  SELECTCOLUMNS(
    FILTER(
      'Focused weeks', // Focused weeks table used as the source of the summarization
      NOT(ISBLANK('Focused weeks'[Focused week (W-n)]))
      ), // only "W-1" .. "W-5" selected
    "Index", 'Focused weeks'[Index], 
    "Focused week (W-n)", 'Focused weeks'[Focused week (W-n)] // only 2 relevant columns selected
    ), 
  "Metric 1", CALCULATE(
    MAX(SUM('Raynet - business cases'[totalAmmount - recalculated currency]), 0),
    'Raynet - business cases - dates'[Date type] = "validTill",
    'Raynet - business cases'[businessCasePhase.value] = "Won"
    ), // column calculating 1st metric
  "Metric 2", CALCULATE(
    MAX(SUM('Raynet - business cases'[totalAmmount - recalculated currency]), 0),
    'Raynet - business cases - dates'[Date type] = "validFrom"), // column calculating 2nd metric
  "Metric 3", CALCULATE(
    MAX(COUNT('Raynet - meetings'[id]), 0), 'Raynet - meetings'[tags] = "offer first presentation"),  // column calculating 3rd metric
  "Metric 5", CALCULATE(
    MAX(COUNT('Raynet - meetings'[id]), 0)
    ) // column calculating 5th metric (4th metric skipped due to different calculation)
  )

The trouble comes when I try to use slicer to filter the dashboard according to the owners of the detailed owners. The following picture show the difference between wrong figures (left side) and right figures (right side - calculeted withou  DAX query, but cannot be used on the dashboard as aggregation to the weeks level is missing before calculting the 5weeks average).

Please can you help me adjusting the DAX query calculating the weekly averages while enabling slicing according to the owners as well?

Thank you very much

Jakub

[lbendlin]

You may want to show your data model, and maybe provide sample data in usable format.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[jakubalbrecht]

Hello, zip file containg PBIX file (without authentication string for the data connected) can be downloaded here: https://www.vcas-os.cz/wp-content/uploads/2024/03/Dashboard-no-auth.zip

[lbendlin]

Thank you for the sample data.  I'm missing a data model.

What you have here is more akin to something you would do in Qlik.  In Power BI it is preferred to have dimension tables and fact tables in a star schema.  I think it is worth investing time in creating a proper data model before addressing the original question.