Row Subtotal of Measure in Pivot table is empty
Dear Community, I am strugling to understand and fix the issue of empty subtotals for the measures I created in the Power Pivot model. Please see below the view of the Pivot table: The YoY measure returns same week previous year sales value (eg. Actual Booking in week 2 of year 2023 ($623K) are reflected as YoY Actual Bookings for week 2 of year 2024). YoY measure synthax: YoY:=CALCULATE([Actual Bookings], (FILTER(ALL(T_Calendar), T_Calendar[Fiscal Year]=IF(HASONEVALUE(T_Calendar[Fiscal Year]), VALUES(T_Calendar[Fiscal Year]))-1&& T_Calendar[Fiscal Week]=IF(HASONEVALUE(T_Calendar[Fiscal Week]), VALUES(T_Calendar[Fiscal Week]))))) Could you please advise why the measure is present in the row of the Pivot table for each week but not visible in Subtotal while other rows in Pivot table are there in Subtotal? What should I do to fix it? Thank you in advance!
Incorrect Subtotal
Hi I have a DAX for custom measure with the formula of Prev Day User Asset USD Latest Rate = CALCULATE(sum('user daily asset'[amount_usd_latest_rate]),PREVIOUSDAY(Dates[Date])) The DAX will give result like below. Where the prev day value of each day is correct, but on the subtotal of the month, its showing the previous day record of the first date instead of the SUM of all the prev day value. How can this be fixed? Thanks
Suggestions to Optimize DAX RollUp and SubTotal query
Hi Team, We want to get the rollup and subtotals across different dimensions .Can you please tell if there is way to optimize it? EVALUATE var t1 = CALCULATETABLE( SUMMARIZE( 'Fact Sales', ROLLUP ( 'Customer'[Name], 'Customer'[Geography], 'Customer'[Region], 'Customer'[Industry], 'Store'[Subsidiary]), "Name total", ISSUBTOTAL ('Customer'[Name]), "Geography total", ISSUBTOTAL ('Customer'[Geography]), "Region total", ISSUBTOTAL ('Customer'[Region]), "Industry total", ISSUBTOTAL ('Customer'[Industry]), "Subsidiary total", ISSUBTOTAL ('Store'[Subsidiary]), "Metric Name", NAMEOF('Fact Sales'[Sales]), "Metric Value", [Sales]), TREATAS ({ "FY22" }, 'Date'[Fiscal Year]) ) RETURN t1 Thanks, AbhiramCalculate the percentage of table matrix as well as subtotals
I need to calculate the Share of the below table, I was able to calculate in Pbi the share per Brand by using this DAX =DIVIDE([Sales_], CALCULATE([Sales_], ALLEXCEPT('tblProduct','tblProduct'[Brand_name]))) However subtotal needs to calculate the share base on column total (sub-total / Column Total) In my case sub-total is returning 100% Requirements: i write here as well the excel formula (Column F) on how to calculate the share and its sub-total.Delete Matrix subtotal when i have 1 line with DAX
Hi guys! We have the following, we have 2 levels, one for country and another for cities. On the other hand, we have a measure that formats the constitution date. I put the screenshot to see the stage: I want to try to hide the constitution date for the country level. If you look at this I have achieved it when the country has 2 cities, but when I have only 1 city in that country it also shows it in the subtotal. For Example Spain has Jan.-20 however France is blank. The measures is the next one: Format Constitution Date = VAR firsconstitutiondate = LASTDATE(Data[Constitution Date]) VAR firsconstitutiondate_format = FORMAT( firsconstitutiondate, "mmm.-yy" ) var result = IF ( HASONEVALUE(Data[City]), firsconstitutiondate_format ) return result any suggestions? Thank in advance. Best Regards.
Measure to Sum specific rows and the subtotal/grand total to still display the Actual Total Sales
Hi Power BI Community, I have a table displayed like below, with column definitions as follows. Tot Sales = Sum(Sales) My Formula = Is my attempt to get the expected result. Expected Result = How I want the calculation to work. Here is the DAX I wrote SUMX(SUMMARIZE(SalesFact,'Territory'[Territory ID],'Product'[Sales Role]),IF('Product'[Sales Role]="Cat 1 Rep",CALCULATE([Total Sales],'Product'[Cat 1 Product]="YES",ALL('Product')),IF('Product'[Sales Role]="Cat 2 Rep",CALCULATE([Total Sales],'Product'[Cat 2 Rep Product]="YES"),CALCULATE([Total Sales],'Product'[Cat 3 Rep Product]="YES")))) The above formula gives me the expected result against the Cat 1 Rep row, but fails to get the expected sub total and grand total. The field "Cat 1 Rep Product" in above formula is a flag which identifies products from product table that fall into Cat 1. "Cat 2 Rep Product" and "Cat 3 Rep Product" are similar columns. What I want is - Sum the results for Cat 1 Rep row and Cat 2 Rep row and display it against the Cat 1 rep, other rows shouldn't be impacted. Also, I do not want this manipulation to impact the Column subtotal or grand total and should display the actual total that is (Cat 1 Rep Product + Cat 2 Rep Product + Cat 3 Rep Product) and NOT ( (Cat 1 Rep Product + Cat 2 Rep Product) + Cat 2 Rep Product + Cat 3 Rep Product). Is this possible? Thank you all!
