Forum Discussion
Measure not populating Rows and only showing Total
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
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.
2 Replies
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
