Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jakubalbrecht
Frequent Visitor

DAX summary of data across multiple tables

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.)

 

jakubalbrecht_0-1706567244493.png

 

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).

jakubalbrecht_1-1706569665981.png

 

 

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

3 REPLIES 3
lbendlin
Super User
Super User

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...

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

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

 

lbendlin_0-1709418327722.png

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.

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.