How to calculate subtotal correctly with hierarchies
Hi I am struggling with the following problem. I have a fact table in which I already receive calculated values per category and subcategory in one table. The totals per category and subcategory do not and should not be equal. Let's assume we have category A, which is divided into 3 subcategories: AA, AB, AC. The entire category A is supposed to have a subtotal of 370, and the subcategory AA is 50, AB 100 and AC 200. The sum of the subcategories is 350, but I would not like to see 350, but 370. I already receive the calculated data from the backend layer: Since I have one column with values that simultaneously contains the sum of the entire category and the subcategory values, the whole thing is duplicated in the sums. How can I approach this differently? This is an example, the real case has a category and a tree with 8 levels of subcategories, so it is very important for me that the solution is calculated correctly.
I need to supress the rows where the column sub-total has no value.
The show items with No data works, if I have not formatted the Amounts in my columns, but once I format it using DAX, this option doesn't work anymore. This one is w/o formatting the amount column This one is after formatting the Amount Columns(Month-Year) My DAX for Visual 2 - What can I add here to suppress rows which have no data in the month columns! Actuals = VAR _selectedHeader = SELECTEDVALUE ('a360_finance fin_PnL_Template2'[Level_1]) VAR _Actuals = FORMAT([Actual Amount],"#,##0.00;(#,##0.00)") VAR _IsLevel2Visible = ISINSCOPE('a360_finance fin_PnL_Template2'[Level_2]) VAR _DisplayDetailCode = SELECTEDVALUE('a360_finance fin_PnL_Template2'[Highlight]) VAR _Revenue = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Sales Revenue"} ) VAR _COGS = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Cost of Products Sold"} ) VAR _CM = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Sales Revenue","Cost of Products Sold"} ) VAR _OCGS = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Other COGS"} ) VAR _SGA = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"SGA"} ) VAR _NOPEXP = CALCULATE([Actual Amount], REMOVEFILTERS('a360_finance fin_PnL_Template2'), 'a360_finance fin_PnL_Template2'[Level_1] IN {"Non Operating"} ) VAR _Results = SWITCH(TRUE(), _IsLevel2Visible = TRUE() && _DisplayDetailCode = 1, Blank(), _selectedHeader = "Revenue", FORMAT(_Revenue, "#,##0.00;(#,##0.00)"), _selectedHeader = "Cost of Goods Sold", FORMAT(_COGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Contribution Margin", FORMAT(_CM,"#,##0.00;(#,##0.00)"), _selectedHeader = "Contribution Margin Percentage", FORMAT((_CM/_Revenue), "#0.0%;(#0.0%)"), _selectedHeader = "Other Cost of Goods Sold", FORMAT(_OCGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Total COGS", FORMAT(_COGS+_OCGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Gross Profit", FORMAT(_Revenue+_COGS+_OCGS,"#,##0.00;(#,##0.00)"), _selectedHeader = "Gross Profit Percentage", FORMAT((_Revenue+_COGS+_OCGS)/_Revenue,"#0.0%;(#0.0%)"), _selectedHeader = "Total SGA", FORMAT(_SGA,"#,##0.00;(#,##0.00)"), _selectedHeader = "Total Operating Expenses", FORMAT(_SGA,"#,##0.00;(#,##0.00)"), _selectedHeader = "Operating Income", FORMAT((_Revenue+_COGS+_OCGS+_SGA),"#,##0.00;(#,##0.00)"), _selectedHeader = "Operating Income Percentage", FORMAT((_Revenue+_COGS+_OCGS+_SGA)/_Revenue,"#0.0%;(#0.0%)"), _selectedHeader = "Total Non Operating", FORMAT(_NOPEXP,"#,##0.00;(#,##0.00)"), _selectedHeader = "Net Income", FORMAT((_Revenue+_COGS+_OCGS+_SGA+_NOPEXP),"#,##0.00;(#,##0.00)"), _Actuals //FORMAT(_Actuals, "#,##0.00;(#,##0.00)") ) Return _Results
Need help on DAX
I have two tables - Sales Invoice Lines containing sales transaction and Ledger table containing all financial accounting entries. Both these tables have voucher as common item and hence a relation is created. i need to populate customer's itemized statement in PBI. I have all the details required in sales invoice except GST and PST. In the sales, tax is total whereas tax is split in two lines under GL 271000 and 274000 in the ledger table. when I create a basic measure and/or column for GST and PST, the visual is bringing the GST for all the transaction. but I want ot display the GST and PSt only for the tax impacted line item. my table is as below. in the above, total tax is applicable only on 1 line item. how do I populate the GST and PST only to those lines. my measures are GST = CALCULATE(SUM('TAX Entries'[TRANSACTIONCURRENCYAMOUNT]), FILTER('TAX Entries', 'TAX Entries'[MAINACCOUNTID]="271000")) and PST = CALCULATE(SUM('TAX Entries'[TRANSACTIONCURRENCYAMOUNT]), FILTER('TAX Entries', 'TAX Entries'[MAINACCOUNTID]="274000")) I have tried columns as GSTAX = IF('TAX Entries'[MAINACCOUNTID]="271000", 'TAX Entries'[TRANSACTIONCURRENCYAMOUNT],0) PSTAX = IF('TAX Entries'[MAINACCOUNTID]="274000", 'TAX Entries'[TRANSACTIONCURRENCYAMOUNT],0). please advise
