Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello Experts
I have a pbix with the below tables
Table Names |
RevenueData |
FreightData |
CreditData |
JEData |
RevenueDataService |
Calendar |
Dim_Application |
DIM_GLAccounts |
With the below Relationships
RevenueData[Date] - Calendar[Date]
RevenueData[Application] - Dim_Application[Application]
FreightData[Date] - Calendar[Date]
FreightData[Application] - Dim_Application[Application]
CreditData[Date] - Calendar[Date]
CreditData[GL Account] - Dim_GLAccounts[GL Account]
CreditData[Application] - Dim_Application[Application]
JEData[Date] - Calendar[Date]
JEData[GL Account] - Dim_GLAccounts[GL Account]
JEData[Application] - Dim_Application[Application]
RevenueDataService[Date] - Calendar[Date]
RevenueDataService[Application] - Dim_Application[Application]
I have a Report View with couple of slicers and a Table
Slicer1 - Year - Linked to the Calendar[Year]
Slicer2 - Month - Linked to the Calendar[Month Name]
The table has columns with the below Measures as columns
Application - Linked to Dim_Application[Application]
Revenue - Linked to 0_MTDRevenue = SUM(RevenueData[Revenue])
Service - Linked to 0_MTDRevenueService = SUM(RevenueDataService[Revenue])
Freight - Linked to 0_MTDFreight = SUM(FreightData[Freight])
Credit - Linked to 0_MTDJECredit = SUM(JEData[Credit])
JE Debit - Linked to 0_MTDJEDebit = SUM(JEData[Debit])
JE Credit - Linked to 0_MTDJECredit = SUM(JEData[Credit])
MTD (Revenue - Credit) - Linked to 0_MTDRevLessCredit = [00_MTDTotalRevenue]-[00_MTDTotalCredit]
% of Rev - Linked to 0_% of Rev = DIVIDE([0_MTDRevLessCredit], [0_EveryApp],0)
YTD Revenue - Credit - Linked to 000_YTDRevLessCr = TOTALYTD([0_MTDRevLessCredit], 'Calendar'[Date])
Not in the table 0_EveryApp = CALCULATE([0_MTDRevLessCredit],ALL(Dim_Application))
My problem is that the YTD Revenue - Credit does not populate data by rows and only shows the total and I need it to show me the totals by rows as well like all the other columns
Apologies havent attached a Sample file as the file is huge and linked to SAP
What am I doing wrong? The YTD measure should work as the MTD measure works
Regards
Rah
Solved! Go to Solution.
First, make sure that your calendar table is marked as a date table. Also, make sure that all the relationships with the calendar table are single direction.
Go into DAX Query view and run
DEFINE
VAR _Filter =
TREATAS ( { ( 2024, "December" ) }, 'Calendar'[Year], 'Calendar'[Month] )
EVALUATE
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL ( Dim_Application[Application], "Is Total" ),
_Filter,
"@num days", COUNTROWS ( DATESYTD ( 'Calendar'[Date] ) )
)
You should see the same number for each application and in the total row. If you don't then somehow a filter is getting from dim_Application to the calendar table.
First, make sure that your calendar table is marked as a date table. Also, make sure that all the relationships with the calendar table are single direction.
Go into DAX Query view and run
DEFINE
VAR _Filter =
TREATAS ( { ( 2024, "December" ) }, 'Calendar'[Year], 'Calendar'[Month] )
EVALUATE
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL ( Dim_Application[Application], "Is Total" ),
_Filter,
"@num days", COUNTROWS ( DATESYTD ( 'Calendar'[Date] ) )
)
You should see the same number for each application and in the total row. If you don't then somehow a filter is getting from dim_Application to the calendar table.
Hi johnt75
That's for your support, one of the relationship was both direction and once i changed that to single it worked
Thanks for your help much appreciated
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |