How to calculate Annualization In Dax? Help needed!
Hi, I am having trouble calculate Annualized Return on Assets. The expected calculation is Earning/Asset*(Selected Month/12). There are two tables: Date Table and Pivoted Financial table shown below. What I hope to achieve is having a dax calcualtion where I could select any given month period the (Selected month) in the bracket will change accordingly. ROA(KPI) = [AA_400000]/(Selected month)*12/[AA_500000] I wonder how this could be done in the dax formula? The output will be a percentage figure. PBIX File: https://drive.google.com/file/d/1qJ8nSbO6pO4fXDgY-YFSrNQOR7oLyzTz/view?usp=sharing Thanks in advance Laurence
Tablix Groups ( I think)
Hi guys I wonder if there is a Paginated report guru out there who can help me with the following. I am writing a pbix to report accounting information (profit and loss, balance sheet)- and all is running well. Fact table with financial data Dim tables for departments and sites Dim table for line structure in right order: ie Report ID, Report Name,Line 1 Turnover, Line 2 COS, Line 3 Gross Profit, Line4 Expenses, Line 5 Dept Profit Then a second reportname and Line6 , Line 7 etc picking up different amounts Relationships created for the above work fine; pbix runs quickly on desktop and in service. So very pleased so far. I am now trying to create a paginated report with the same data, but am struggling with the grouping. This is what I have so far: A "fresh table" to manage the production of reports, (not all sites have all departments) with no relationships to the main model. Columns are: 1. Recipient - (to create parameter for final running of report) 2. Site 3. Report Name 4. Department 5. Sub Dept I have got the report running with group hierarchy of Site, Dept, Report name, Line Ref. and am linking the main SSAS dataset to a dataset of the "Fresh Table" with Lookup and LookupSet. Kinda works but hard to write! Problems are: 1. We need the first few pages to report on total numbers for the site, but this doesn't seem to work with the above hierarchy. 2. We need also in some departments but not others to report on sub departments and dept totals too. So question is how do I manage the grouping so that for certain reports we add up all departments, for others just departments and for some departments (totalled) and sub departments. Or do I need to approach it in a different manner. One workaround might be to create multiple tablixes; 1 for site summaries, 2 for depts, 3 for sub depts. but this seems a bit clunky, the rep will probably run slowly or fall over and there has to be a better solution. Any help greatly appreciated. Thanks Pete 3. Report ID
