Unable to get the YTD number for a given total
I'm working with a trial balance which gives output in YTD format i.e. it rolls up the numbers from Jan to whichever month you are downloading the data for. Now I want to work our the amounts for the month for which I have written the below dax Below is my Base data named "OS TB_Input" which is granular to the GL and Cost Centre and I have a Dimention table for both GL and Cost centre separately and mapped to this Fact table along with a Calendar table mapped to the Month Column. Amount is the column with YTD numbers for each particular month for that GL/ Cost Centre. "[Trial Balance]" just sums up the amount column which is YTD numbers. Trial Balance = SUM('OS TB_Input'[Amount]) Monthly is a measure created to find the monthly values Monthly = VAR PrevMonth = CALCULATE( [Trial Balance], PREVIOUSMONTH('Calendar'[Date]), COA_Grouping[P&L/ BS] = "P&L" ) VAR CurrentMonth = CALCULATE( [Trial Balance], COA_Grouping[P&L/ BS] = "P&L" ) RETURN IF( ISBLANK(CurrentMonth), BLANK(), [Trial Balance]- PrevMonth ) Although this gives the monthly numbers correctly but it messes up the totals as what I expect in the total yearly column is the sum of all the Months whereas it's showing up a much different number. Pl help to correct the totals and also if someone could suggest how to improve the dax, that would be of great help.
Total Is Correct but row is not when using ALL
Biggest puzzler yet: I have this measure called [%Need]. The sum total of these percentages needs to be 1 in order to compute correctly. However, it's not, so in order to correct it I need to do a formula like: [%Need]/Sum [%Need] *1. So in order to get sum portion I did: CALCULATE(SUMX(VALUES('Calendar'[calendar date]),[%Need]),ALL('Calendar'[CalendarDate])) This puts 1(I checked it out to 15 0s 1.00000000000000 in each row instead of the correct amount which is 1.0147. 1.0147 however appears in the Total at the bottom. I need 1.0147 in each row so I can have it divided by [%Need]. This probably doesn't need to be included, but just in case the [%Need] measure is: IF([CALendarDATE]>LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds]),[PYGA]/([PYALLGAs]-[GAsLyMAXDateAll]),Blank()) Any idea how to get the correct total in each row?Distinctcount + sumx throws strange results
Hi All, I have been breaking my head around this one, hope somebody can help me understand. I have an issue with a Dax formula, I hope you don't mind I added an example in Excel PowerPivot rather than PBI... Attached file contains data sample, 1 measure and a pivot table. Result is as follows: Can somebody explain me the 122...I would totally expect 92 there...I am aware of how SUMX works, I am aware that totals are not always a sum of the above, and I feel like I am missing the obvious here, but I think I am in somewhat of a rabbit whole here 😉 Who can help me undertand. Book1.xlsx For the grand total, I would expect that VALUES(PropertyID) iterates over Property 1, counts 61 distinct dates...then Property 2, counts 31 disntinct dates, then sums them to 92... Thx JanCalculate in measure at column won't filter other objects
Hi! I placed this measure in "values" on a Matrix: measure = CALCULATE(sum(Fact[Totalsum]),Fact[type] = "D") But when i select anything within this matrix it will filter all objects from the model excluding Fact[type] = "D" and showing the absolute total (Measure total is selected) I also tried using SumX but didn't work: measure = SUMX(FILTER(Fact,Fact[type] = "D"),Fact[Totalsum]) Why could this be?
Incorrect Measure Total on Matrix Visual
Hi, I have the following the following report page with a date slicer and a table. - The date slicer comes from a date table that DOES NOT have a relationship with my facts table. -The "Max" date in the date slicer is used for the "Calculation" measure (DAX below), which essentially is a SWITCH Function that handles the following conditions. 1) If "End Date" <= "Max" date in the date slicer then return the full value of "Dollars" 2) If "Start Date" = "Max" date in the date slicer then return daily rate of "Dollars" (Dollars / (End Date - Start Date) 3) If "End Date" > "Max" date in the date slicer then return the daily rate * ("Max" date in the date slicer - Start Date) Calculation Dax SWITCH( TRUE(), MAX('Fact Table'[End Date]) <= MAX('3.dim_date_table'[Date]), SUM('Fact Table'[Dollars]), MAX('Fact Table'[Start Date]) = MAX('3.dim_date_table'[Date]), DIVIDE(SUM('Fact Table'[Dollars]),(DATEDIFF(MAX('Fact Table'[Start Date]),MAX('Fact Table'[End Date]),DAY)),0), MAX('Fact Table'[End Date]) > MAX('3.dim_date_table'[Date]), DIVIDE(SUM('Fact Table'[Dollars]),(DATEDIFF(MAX('Fact Table'[Start Date]),MAX('Fact Table'[End Date]),DAY)),0)*DATEDIFF(MAX('Fact Table'[Start Date]),MAX('3.dim_date_table'[Date])+1,DAY) , 0 ) On a row by row basis, everything appears to be working as expected. The only issue is that the total amount is not accuate. For example in the screenshot below, we see that the Calculation total is $5,934.01 when it should be $6,319.61 I suspect this is due to the lack of row context when referring to the "Max" start and "end" dates for the total calculations, so the max values of the entire dataset are used rather than calculating row by row. Here is a link to the pbix file: https://drive.google.com/drive/folders/1GjFTpaLMW-6vf36LFPpHR_IHuhtaAcPL?usp=sharing Please let me know if any additional information is needed. Any input is appreciated. Thanks!Measure shows wrong total, does not take into account all Months
Hello Community, I created a Measure which forecast values for Months which currently do not have any Data. In a Matrix it shows the correct results for exisiting months(current value of a measure) and for months, where the Measure is currently blank. But the total is wrong. It still only show the total for the existing months. The measures are based on my facttable. The Fact table currently has data until end of August. There are no rows for Months after august. Also when i put the Measure as a Column in my Date Table, it only returns the results for months which are available in the Fact Table.
