How to add Calculated rows in a PBI Matrix
Hi all, I want to insert calculated rows to a power BI matrix. How do i add this? I can add calculated columns. But not sure how to structure the data and what are the functions to use to do this; 2018 Vol(KG) 100 Turnover 12,000 Cost (8,000) Gross Profit 4,000 Cost/KG 40 Gross margin 33.3% This is what the matrix should look like. Cost/KG and Gross margin are calculated using above rows. Please assist
Displaying a Row as Fixed, when it's value needs to be manipilated
*I have trouble converting the data type of Column Amount in Source ABC to Decial/Numbers for this tutorial(help needed), see data source:https://drive.google.com/file/d/1VZJv8sPPYPcLiRKyO1fkEqbcsX-dz10P/view?usp=sharing . PBIX:https://drive.google.com/file/d/1wfjbLI7nBLQU5-Qs5rGHOL2XP4uc0UR9/view?usp=sharing I am trying to include rows in a Dax calculation from a mapping table(Pivot) to a fact table(ABC). The DAX is going to look at specific rows and bring those forward. The big issue is some rows are not present in the Fact table. I also need those rows to be displayed but their values to be manipulated. An example is row "Interco capital returned", which is not in the Fact Table(ABC), it should display a fixed value of "678" TotalAmountForSelectedItems5 = CALCULATE( SUM('ABC'[Amount]), // Corrected Column Reference 'ABC'[Roll_Up_Function] IN { "Cash flow from ops - management", "Cash flow from trading", "Depreciation and amortisation", "IPEP expense", "Disposals & impairment of fixed assets", "Profit on disposal of pooling equipment", "Scrapped pooling equipment", "Impairment or valuation adjustment of pooling equipment", "Disposals or valuation adjustments of other fixed assets", "Other cash flow from trading adjustments", "Share-based payments expense", "Working capital mvts incl. provisions", "Working capital mvts excl. provisions", "Debtor movements", "Creditor movements", "Inventory movements", "Prepayment movements", "Provision movements", "Change in capex creditors", "Change in loss compensation balances", "Interco interest and guarantee fees", "Interco cash flows", "Interco royalties", "Statutory reallocations", "Internal restructuring", "Interco dividends Total", "Change in interco balances", "Change in interco recharge clearing", "FX on interco debt", "Interco capital returned" } ) amitchandak jpessoa8 lbendlin
filter one column in one table to another column in another table
Henlo, I am needing to add a measure that filters out data from my matrix. I am needing only rows returns that match this criteria ( {IM_ItemWarehouse.WarehouseCode} = {CI_Item.DefaultWarehouseCode} ) I know how I can do this in power query but I have other visuals that do not require the same filtering. Can someone help me with how I would create a measure to accomplish this? Oodles of thanks in advance! 🙂Dax measure to compare dates in different rows based on a measure
This is my first time posting, so bear with me. I have a dataset like below: I have the following two measures that calculate running totals. These running totals change based on the current filter context (Category) slicer. Actual running total (Measure) = CALCULATE( SUM('Table1'[Actual]), FILTER( ALLSELECTED('Table1'[Date]), ISONORAFTER('Table1'[Date], MAX('Table1'[Date]), DESC) ) ) Baseline running total (Measure) = CALCULATE( SUM([Baseline]), FILTER( ALLSELECTED('Table1'[Date]), ISONORAFTER('Table1'[Date], MAX('Table1'[Date]), DESC) ) ) I need to create a measure that identifies "Days Delay" for each row. For each row I need to take the "Actual running total (Measure)", find the Max "Baseline running total (measure)" that is less than the "Actual running total (Measure)" and then compare the differences in dates. Let's take row "05 January 2019" as an example. Take "Actual running total (Measure)" = (8) Find Max "Baseline running total (measure)" that is less than "Actual running total (Measure)" = (5) Get the corresponding date = (03 January 2019) Compare (Datediff) with original Row Date (05 January 2019) = (-2) The big challenge is that the solution needs to be dynamic i.e. if I change the "Category" slicer, the measures need to recalculate and produce the relevant results based on the context of the slicer / filter. Any direction or help would be greatly appreciated.
Multiple row and Column Add up (Remove Duplicates)
I am currently working on a project using a grading system and want to display total% of an individuals skillset. The grading system works using the below image (I've only used my own test ID but in the data set it is showing all 930). Scenario 1 = 10%, Scenario 2 = 20%, Scenario 3 = 30% and Task 1 + Task 2 both give 20% each resulting in the individual being 100% trained. The main issue with this is that the user can have multiple entries for each header, resulting in the total% being over 100% in some cases. Is there a measure that can be implemented to just look at each of these columns and just take one entry from each and ignore duplicates while adding up to a max of 100%? Happy to give more details.Transformed table with new rows
Good day everyone. I need to make transformed table using data from next table: IDLE_ID START_DT START_TIME END_DT END_TIME IDLE_TYPE 122851 09.09.2019 05:57:00 01.10.2019 09:00:00 Planned 123111 01.10.2019 09:30:00 02.10.2019 17:00:00 Planned I need to make visualization like this (abridged example): IDLE_ID START_DT START_TIME END_DT END_TIME IDLE_TYPE 122851 09.09.2019 05:57:00 09.09.2019 24:00:00 Planned 122851 10.09.2019 00:00:00 10.09.2019 24:00:00 Planned 122851 11.09.2019 00:00:00 11.09.2019 24:00:00 Planned 122851 12.09.2019 00:00:00 12.09.2019 24:00:00 Planned 122851 13.09.2019 00:00:00 13.09.2019 24:00:00 Planned 122851 14.09.2019 00:00:00 14.09.2019 24:00:00 Planned 122851 15.09.2019 00:00:00 15.09.2019 24:00:00 Planned 122851 25.09.2019 00:00:00 25.09.2019 24:00:00 Planned 122851 26.09.2019 00:00:00 26.09.2019 24:00:00 Planned 122851 27.09.2019 00:00:00 27.09.2019 24:00:00 Planned 122851 28.09.2019 00:00:00 28.09.2019 24:00:00 Planned 122851 29.09.2019 00:00:00 29.09.2019 24:00:00 Planned 122851 30.09.2019 00:00:00 30.09.2019 24:00:00 Planned 122851 01.10.2019 00:00:00 01.10.2019 09:00:00 Planned 123111 01.10.2019 09:30:00 01.10.2019 24:00:00 Planned 123111 02.10.2019 00:00:00 02.10.2019 17:00:00 Planned I know, it looks really strange, but i need this visualization for easy count of idle for each day. Also first table was formed in DirectQuery, and i need not to change initial table. Any suggestions come useful. Best regards Alexandr
