Distinct Count of Total Averages
Hello everyone, I have a spreadsheet with temperature records from several loggers. I need to find out how many loggers have an average temperature above a certain value (temperature rounded down without decimals). I can calculate this using a combination of a pivot table and Excel formulas, but I can't create a calculated field or measure in the pivot table (PowerPivot) that would do this automatically and of course dynamically based on the filters and slicers used, as you can see in the attached file. Distinct Temp.xlsx
Measure for powerpivot to create average of utilization of total population of different field
Hi All - I am hoping someone can help me with a question rather quickly. I'm trying to calculate the average of the Sum of "Total Units" in an entire population. My current measure formula is only displaying the average of total units of the total population at the CPT Code level opposed to the average units of the CPT Code at the entire population level (field "Claim Number"). The challenge that exists is there could be "Claim Numbers" that do not contain specific CPTs. However, I want the calculation of the "average units" of the CPT utilization to take into account the ENTIRE population so the average is representative of the entire population. I'm not well versed at editing tables outside of excel, so my preference is to use a measure formula. However, if this cannot be achieved be creating a powerpivot measure I am open to trying to trying something through the "manage" Data Model. The other option is to paste all CPTs for all claim numbers with a unit frequency of zero, but that would not be efficient for future updates and also likely get into the millions of rows scenarios. Current Measure Formula '=DIVIDE(Sum('Range 1'[Units Billed]),DISTINCTCOUNT('Range 1'[Claim Number])) Example current output (incorrect as it is only calculating the average utilization of the total of CPT units (97530) Example Pivot Structure Appreciate the help and consideration! Thank you, Sam
How to count a rank
Hello, I like to count the rank of prices. I have the following (example) table: article price Account_No Account_Name Group1 Group2 A 9 60000 Hans 525A EPS A 10 50000 Meier 525A EPS A 11 40000 Schulz 525A EPS A 12 30000 Friedel 525A EPS A 13 20000 Thomas 525A EPS B 20 60000 Hans 525A EPS B 22 20000 Thomas 525A EPS B 10 40000 Schulz 525A EPS C 30 50000 Meier 525A EPS C 32 60000 Hans 525A EPS C 11 40000 Schulz 525A EPS D 12 50000 Meier 925A EPS D 13 40000 Schulz 925A EPS D 10 30000 Friedel 925A EPS E 9 20000 Thomas 925A EPS E 20 60000 Hans 925A EPS E 22 20000 Thomas 925A EPS E 10 40000 Schulz 925A EPS F 30 50000 Meier 925A EPS F 32 60000 Hans 925A EPS With the both measures: Price_:=MAX(Prices_History[price]) Preis_Rank:=RANKX(ALLSELECTED(Prices_History[Account_Name]);[Price_];[Price_];;Dense ) I can analyze the data in a PivotTable like this (works perfekt): Now, what I need is (a measure) to COUNT the Rank per ACCOUNT and Group1, this should be like this: (I think) I can't use a calculated column with the rank, because I will use a slicer for account and Group2, so I need a dynamic rank. Can anybody help with this?
How to have dynamic aggregation in PowerPivot?
Hello I create a pivot in Excel and I put a field in the 'Values' area of the pivot. There I have to choose the type of aggregation, like sum, average etc. Is it possible to choose different type of aggregation when a specific field contains a *string* and another type of aggregation for the other fields? For example, my raw data is: KPI Date Value Sales 01/01/2021 142 Sales 02/01/2021 163 Sales 03/01/2021 184 Sales 04/01/2021 205 Sales 05/01/2021 226 Stock 01/01/2021 164 Stock 02/01/2021 172 Stock 03/01/2021 180 Stock 04/01/2021 188 Stock 05/01/2021 196 After I pivot it, it becomes: Sum of Value Column Labels Row Labels 01/01/2021 02/01/2021 03/01/2021 04/01/2021 05/01/2021 Sales 142 163 184 205 226 Stock 164 172 180 188 196 Grand Total 306 335 364 393 422 How can I make it to sum the Sales and average the Stock? Thanks!
RANKX depending on filter context
Hi all, hours later I still haven't been able to figure out how to show only the top2 values by rank depending on the filter context. I can get it to work for PeerGroup set to All or I can get it to work for ONE PeerGroup selected but not for both scenarios. It's driving my absolutely nuts. Any ideas??? Using Powerpivot, not Desktop appCumulative sum in column until it finds the certain value
Hi, I need to write this to the Explicit measure in the Power Pivot: Start with cumulative sum in column (One by one; It can be ascending or descending.), when it hits the first 0 in the cell, stop the calculation and show the sum from 1 row to the row where is the 0 placed. Can anybody help with the measure? I have only 0 or 1 in the Column. When it is needed I can replace 0 for „Null“ or some text. Thank you, Jan Ps: I additionally added the picture and sample data. Sample data: https://ulozto.cz/tamhle/MNXyzonmetMQ#!ZJZkMwR2AzHlZGpkZwx2BGIuAzZ3Z1SAqTIlH0cBMGuiomOxZD==
Refresh Excel workbook with PowerPivot
Hi, A while ago the Roadmap for PBIRS had a feature with the ability to refresh Excel with PowerPivot. It's not there anymore, so i guess that feature isn't coming anytime soon. chrisfin had a blogpost showing how we could use a thrid party tool called PowerUpdate to refresh the Excel workbooks. We prefer not using 3. party tools. He also mentioned that anyone could do this with the REST API. So my question is: Is the ability to refresh Excel workbooks with PowerPivot coming natively to PBIRS (anytime soon)? ...Or does anyone have a script which refreshes Excel workbooks on PBIRS that they would like to share with the community. Regards TaicoPowerBI Report + OOS: Excel with AS works, but Excel with PowerPivot fail
Dear Masters, We setup PowerBI Report Server(15.0.1102.235) and Office Online Server(OOS) is one server, also intsall 2 SSAS instances(SQL2012), one for tabluar model, another for PowerPivot mode in same server. We uploaded 2 excel files to PBIRS, ones' source cnnected to SSAS tabular model, it could work well, like add/move dimension. But another file with PowerPivot, it can be open, when we add dimension into Pivot table, error message prompted: We cannot locate a server to load the workbook Data Model. It look seems OOS cannot connect to SSAS PowerPivot mode, we check log file, here is error message: Checking Server Configuration (twtpewgdwdb02d\POWERPIVOT) ... 05/09/2019 11:06:46.53 w3wp.exe (0x20DC) 0x4A88 Excel Online Data Model sql01 Medium --> Check Administrator Access (twtpewgdwdb02d\POWERPIVOT): Pass. Check Server Version (twtpewgdwdb02d\POWERPIVOT): Pass (14.0.1.278 >= 11.0.2800.0). Check Deployment Mode (twtpewgdwdb02d\POWERPIVOT): Pass. Check Server Configuration (twtpewgdwdb02d\POWERPIVOT): Pass. SSPM: Initialization failed on server twtpewgdwdb02d\POWERPIVOT: Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: The '\MSOLAP$POWERPIVOT:Memory\Memory Limit High KB' performance counter could not be found. Refer https://docs.microsoft.com/en-us/sharepoint/support/experts-admin/powerpivot-performance-counter-errors, there is no "Disable Performance Counters" in system registry, so...we add manually, but still not fix the error. Finally, follow isstruction, we try to re-create the performance counter, run below command: unlodctr msolap$pwerpivot Error message prompt: Unable to open driver SYSTEM\CurrentControlSet\Services\msolap. Is anything wrong or any suggestion? Many thank!
Cannot Connect to a PowerPivot Data Source from Report Server
Hi, I am trying to move from SharePoint Integrated mode to PBI Report Server. What used to work: - SharePoint with PowerPivot installed - Upload an Excel with PowerPivot data model to SharePoint - Create a SSRS report, indicate the data source as the Excel uploaded to SharePoint (e.g. Data Source="http://sharepoint/mydata.xlsx"). - In Report Builder, I can access the data including the DAX measures etc. What I am trying to do: - Set up Power BI Report Server - Set up Office Online Server (OOS) with Power Pivot configured - Upload the same Excel file to PBI RS - Connect the SSRS report to the PowerPivot file (e.g. Data Source="http://myReportServer/reports/excel/mydata.xlsx") - 404 file not found error I am able to view the Excel file directly in PBI RS with Excel Online configured but just cannot connect to the PowerPivot data model from SSRS. Thanks.
