Challenge with Month-Year filtering/order without creating new
If I am correct, this is more specific to Power Pivot (where my data are located) and not PBI where this maybe is not issue. I have dim table "plan per employee" for each quarter. In this plan, I would like to count added new customers to the the plan during quarter and to show it per employee by quarter/by month by correct month order. Issue I have, and don't know how to bypass it without to create new dim table with Month-Year (I already have one dim Month-Year which is filtering dim Date for many other calculation and cannot filter "plan per employee with active connection): How would I be able to count added customers per quarter and show it in correct month order with the already created Monht-Year table? Once I created new DimTable Month-Year I can simply do OrderB in data model and as this table directly filter "plan per employee" I don't have any issues. Would this somehow be possible with already existing "old" Month-Year table with inactive connection in order to decrease number of Dim Tables? for now I have simply calculation COUNTA([DateAdded]), i tried with some DAX, but i am lost... Thank you to anyone who could help me out...
Power Pivot - Calculated Column - How to find the 2nd minimum value based on other column groups
I'm having trouble creating a Calculate Column in Power Pivot. I have a table that shows bid results from multiple suppliers for multiple items and divisions. Through research I've been able to figure out the DAX formula for the minimum bid (by Division, by Item). =CALCULATE(MIN(Table1[Bid Cost]),ALLEXCEPT(Table1,Table1[WAREHOUSE / DIVISION],Table1[ITEM NAME])) I was also able to figure out the percent difference based on the lowest bid. =(Table1[Bid Cost]-CALCULATE(MIN(Table1[Bid Cost]),ALLEXCEPT(Table1,Table1[WAREHOUSE / DIVISION],Table1[ITEM NAME])))/Table1[Bid Cost] I'm now being asked if I can do the same for the 2nd lowest bid (by Division, by Item) to place in a Pivot Table for when we make negotiation calls. I've been looking for a couple days through the forum and google, and cannot seem to find an answer for the 2nd lowest value Calculated Column. In Excel, the formula would be: 2nd Lowest Bid =SMALL($D$5:$D$13,2) % From 2nd Lowest Bid =(D5-SMALL($D$5:$D$13,2))/D5 Here is my data sample. I apologize, I'm trying to figure out how to attached the sample data file, but my OneDrive and GooglDrive is blocked by IT security.Combining two tables in a pivot chart / calculating SUM table 2 based on row label of table 1
Hey everybody, I assume this shouldn't be a big deal but I've already spend hours on this and I'm not able to figure it out on my own. I've two tables which I'd like to combine in a pivot chart, including claculations between both tables. I'd like to have a pivot chart, showing: x-axis: products (working ✔️) y-axis (main): Distinct count of serial numbers of serviced products (working ✔️) y-axis (secondary): Values on x-axis (main) in relation to build products (not working ❌) The example file can be found here: example_combine_two_tables_in_pivot_chart.xlsx Input data (the two tables) - worksheet "Input_data" table "tbl_build_products" - which products where build in which year and in which amount (multiple entries, not aggregated by year / product) Product build year amount of products build A 2020 1 B 2020 1 C 2020 2 A 2020 2 B 2020 3 C 2021 3 A 2021 4 B 2021 4 C 2021 5 A 2021 5 table "tbl_serviced_products" - which products where serviced when (multiple entries per product and serial number possible) Product serial number service year A S1 2020 B S2 2020 C S3 2020 A S1 2021 B S2 2021 C S6 2021 A S7 2020 B S8 2020 C S9 2020 A S10 2020 B S2 2021 C S12 2021 A S13 2021 B S14 2021 C S15 2021 A S16 2020 B S17 2020 C S18 2021 current data model I've marked the columns which contain identical values. How it looks like at the moment - worksheet "Pivot_table_chart" blue bars = Distinct count of serial numbers of serviced products (working ✔️) orange bars = Instead, here I'd like to show the values on x-axis (main) in relation to build products (not working ❌). Currently, the orange bars are representing the sum of build products in total, not according to the corresponding row labels. Underlying, there's a measure which is currently summing column "amount of products build" in table "tbl_build_products". This measure looks like this: =SUMX(VALUES(tbl_build_products[amount of products build]);[Sum of amount of products build]) What I'm trying to achieve (next steps) Change measure in a way that I'm getting the sum of column "amount of products build" in table "tbl_build_products" but using the row labels as a kind of filter Calculate the percentage of "Distinct Count of serial number" in relation to all build products of the same product (see Nr. 1) Instead of the orange bars, show the percentage value of Nr. 2 as a line diagram on the secondary y-axis I'm open to other ideas to achieve my goal. I'm looking forward to your assistance! Thanks 🙂 edit I've created another table where I've used regular excel formulas to show what I'm trying to achieve in the end. My problem is that the real dataset is more complicated and the helper column "helper_column_count_distinct" I've inserted into "tbl_serviced_products" (worksheet Input_data) only works as long as all entries are valid. As soon as I start filtering those entries in a pivot chart using another column, the values in the helper column wouldn't be correct anymore. That's why I'm trying to make it work in a way where I can use the full power of a pivot chart. Here is the updated example file: example_combine_two_tables_in_pivot_chart_2.xlsx Input tbl_service_products with helper column Product serial number service year helper_column_count_distinct A S1 2020 0,5 B S2 2020 0,3 C S3 2020 1 A S1 2021 0,5 B S2 2021 0,3 C S6 2021 1 A S7 2020 1 B S8 2020 1 C S9 2020 1 A S10 2020 1 B S2 2021 0,3 C S12 2021 1 A S13 2021 1 B S14 2021 1 C S15 2021 1 A S16 2020 1 B S17 2020 1 C S18 2021 1 Mockup end result using regular excel formulas - worksheet "input_combined" tbl_input_combined product build products amount total serviced products distinct serial number rate of serviced products A 12 5 42% B 8 4 50% C 10 6 60% This results in this pivot table / chart (that's basically what I'd like to achieve without using a helper column and regular forumas):
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!Sell out average
Hola, Ojalá me puedan ayudar. Soy nuevo en Power BI. Configuré relaciones entre tablas, entre ellas la tabla "WEEKS" y la tabla " Sellout Qty" Tabla WEEKS Tabla Sellout WK MTH WK STORE_ID SKU QTY WK14 WK14 11111111 9999 5 WK15 WK15 22222222 8888 7 WK16 WK16 33333333 7777 1 .... .... Mi Pivot está confeccionada de tal manera que tengo en las columnas el MTH, las WEEKS y enseguida los valores. Quedando una vista así APR SKU WK14 WK15 WK16 9999 1 2 3 8888 1 2 3 9999 1 2 3 Ahora, con un campo calculado con power pivot cree la siguiente función que me suma segúnlas últimas 4 semanas desde que creo el reporte: =CALCULATE(SELL_OUT[Sum of SELLOUT_QTY];SELL_OUT[WEEK]="WK"&(WEEKNUM(TODAY())-1) || SELL_OUT[WEEK]="WK"&(WEEKNUM(TODAY())-2) || SELL_OUT[WEEK]="WK"&(WEEKNUM(TODAY())-3) || SELL_OUT[WEEK]="WK"&(WEEKNUM(TODAY())-4)) Si bien está sumando correctamente, la forma en que lo estoy mostrando básicamente me está replicando la misma data que la del sell out en esas semanas. Me ayudan por fa? Además de sumarlas me gustaría sacar su promedio.
dynamic date table based on min and max of date column
I would like to create a date table that is based on the customer's "Sign-up Date." As I do not wish to have dates in my pivot table from before the first sign-up or after the last sign-up, I want the date table to be dynamic and grow as more dates are added. I could certainly "hard code" the start date of the table since it would coincide with the company's opening, but I need the end date to go to the last sign-up date and not show anything in the future. How can I create a Date Table that grows based on the MAX date found in the "Sign-up" column? Note: I typically just use a linked table from Excel for my date tables in PowerPivot but this has proven problematic as I need to include ALL dates between min and max sign-up in my columns and rows even if there is no data found for them when I slice the data (i.e the resulting pivot table needs to have the same number of columns and rows regardless of the slicers applied).DAX measure for calculating open values between two dates - Power Pivot
Hi, New to Power Pivot and DAX so please excuse if this isn't the correct place to post or my question is utterly ridiculous. I have a table that has a list of purchase orders. The table includes two date columns, one for the date the PO was opened and the other for the date the PO was closed. I also have a dimension table that lists months What I'm wanting to do is create a pivot table using power pivot that shows the open value of purchase orders at a point in time determined by the mth and lists the open values as follows (open value is sum of Amount where open_date <= Month & close_date > Month): I'm unsure how to define this in a measure in Power Pivot as I'm not sure how to link it to the month table. I've tried a few things and even just trying something simple like only using one date I can't work out... Ideally I'd like a measure that looks at both dates or if recommend a better way to be looking at this. Any help would be appreciated as again, I'm very new to this. thanks!
