Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
11 | |
10 | |
10 |
User | Count |
---|---|
19 | |
14 | |
13 | |
11 | |
8 |