This topic contains 8 replies, has 2 voices, and was last updated by 
-
Posts
-
Hi Tom
Finally Found it 🙂
I have been reviewing all methods of abc analysis on the web for DAX, quite complicated.
Hope there are simpler ways to go about this within DAX.
Doing this exercise manually across the whole business by site and division is not practical.
I have a single denormalized table with 76Mil rows which contains sales data,site, division I need to create a simple sum by division and site and then base my abc analysis of this using the earlier function for each site I still can not create simple sum of product by division and site.
Hope someone can help
There are several ways this could go, but if you were looking for a formula that would be used inside of another formula, either of the following should work (critical though that the column added is based on a measure or on a CALCULATE()):
Either
ADDCOLUMNS ( SUMMARIZE ( Example1, [Product_No], [Division], [Site] ), “COS”, [Total_COS] )
or
ADDCOLUMNS ( SUMMARIZE ( Example1, [Product_No], [Division], [Site] ), “Sum of COS”, CALCULATE ( SUM ( Example1[Cost_Of_Sales] ) ) )
If you do not use a measure or a CALCULATE for the column expression, you will not get the benefit of context transition. If you are not familiar with context transition, let me know.
One of the other options available to you with Excel 2013 or later, would be to use the EVALUATE statement on the Excel side of the workbook to query the results from Power Pivot using one of the formulas above AND THEN use that result set as a linked data source back into Power Pivot (take a look in the Power Pivot window where one of the link back tables was added). In the attached workbook, on the tab that says “Link Back Tables”, right click in a single cell in either table and in the popup menu select the Table… option and then choose Edit DAX to see the query using EVALUATE. Something similar could be done with Power Query using the Group By feature (and may be preferable to the link back table). Link back tables do refresh on data refresh.
Thanks for the response
This is a great function(did some reading on it) thanks, similar to SQL group by, for some reason the function complains about multiple columns, please see attached.
No i am not familiar about the context transition?
So i am using a SQL connection to my workbook, i can do the group by in sql, but i would like to do it in powerpivot for other analysis.
Regards
I understand the linked back tables also great idea, my table will exceed the 1.1 million line limitation in excel?
Hi Tom
I managed to get the summarized values via sql table. but my earlier function applies the values to the whole table instead of resetting my by every site and division.
Is there a way i can reset my earlier function at each site and division?
Sorry about all the mails
I think I am making some ground 🙂
I managed to shrink to file to 1.2 million rows with my summarized values but whenever I insert the following calculated column with :
=CALCULATE ( [Total_COS], ALL ( ABC), ABC[cost_of_sales]>= EARLIER( ABC[cost_of_sales] ) ) / CALCULATE ( [Total_COS], ALL ( ABC ) )
It runs forever and eventually bombs out….?
I also suspect this will give me the total abc percentage for the whole table instead of each site and division?
please see attached
Regarding questions:
The message regarding multiple columns in this case was raised because the formula returns a table, not a single value (a measure must return a single value). When a formula returns a table it can either be used as an interim result inside of another DAX formula or, when combined with the EVALUATE statement, can be used as a query on the Excel side of a workbook against the data model.
Context transition is an enabler for many other features in Power Pivot, but until one knows how it plays out, its effects can give measures unexpected results. Context transition is a feature that is invoked any time you use an iterator like FILTER or the X functions either with a measure or a formula wrapped inside of a CALCULATE. Context transition takes a row context and converts it into a filter context and the next paragraph explains why this is a big deal.
Row context gives Power Pivot the ability to handle one row at a time, and by itself, has no effect on filtering across model relationships. When context transition is invoked using an iterator combined with a measure (or a formula that is wrapped inside of a CALCULATE), the row context is converted into a filter context and now WILL have a filtering effect across model relationships. Both example formulas use context transition to extend the filter of Product, Site and Division across the data table (one formula uses a measure, the other formula uses a CALCULATE). Had a formula been written without context transition, such as:
ADDCOLUMNS ( SUMMARIZE ( Example1, [Product_No], [Division], [Site] ), “Sum of COS”, SUM ( Example1[Cost_Of_Sales] ) )
the result for [Sum of COS] across each row would be the grand total. See the third table added to the “Link Back Tables tab” which uses the formula above and delivers the WRONG results.
I believe your model and formula will not give you the results you are looking for. I picture instead starting with a multi-table model (fact table + lookup tables).
Tom
Thanks Tom
I did some research on the link back tables, thanks for this.
I see what you mean with multi table, makes more sense, thank you for the all the replies.
The Table function in Excel is great but can you only call in a table by site? or do you have to make use of the rollup function? The rollup function also seems to be giving me issues.
Please see attached
Regards
You can also do a SUMMARIZE without the ADDCOLUMNS, it is just not as performant. So to use ROLLUP with SUMMARIZE, the formula cannot use ADDCOLUMNS:
EVALUATE
SUMMARIZE ( Example1, ROLLUP ( [Product_No], [Site] ), “Sum of COS”, CALCULATE ( SUM ( Example1[Cost_Of_Sales] ) ) )Please see attached.
The forum ‘Power Pivot’ is closed to new topics and replies.