Summarize results of Dax functions

G

gand3rson

New Member
Joined
Jul 28, 2011
Messages
35
I have a dax function (part of the pivot table). It takes data from two different tables and calculates a ration (it takes sales in units from a sales table and units on hand from an inventory table and calculates months of sales on hand.

The stat is calculated at the item level. I then used an if function to categories each style to a bucket based on the "months of salsas calculation". Know that each item has a bucket of grouping I want to roll up the results. In other words I want have table that shows the buckets and the inventory cost in each bucket. But when I do this roll up in power pivot everything breaks. My guess is that my dad formula stops calculating things at the style level.

Is there anyway to get the Dax formula to keep calculating data at the style level even if I want to just show the summary results?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Most of the times, you can chane the granularity (level of detail) of a calculation via the firms parameter of an AggregateX function (SUMX, for example). You can do that even if all you need is to show the results.

Can you post a sample of your data?
 
Upvote 0
JavierGuillen said:
Most of the times, you can chane the granularity (level of detail) of a calculation via the firms parameter of an AggregateX function (SUMX, for example). You can do that even if all you need is to show the results.

Can you post a sample of your data?
Click to expand...

Thanks I tought sumx might have the key but cant get it to work

Table1 looks like this
[TABLE="width: 225"]
<tbody>[TR]
[TD]Inventory table[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location#[/TD]
[TD]item #[/TD]
[TD]Onhand units[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody><colgroup><col><col span="2"></colgroup>[/TABLE]


Table 2 looks like this
[TABLE="width: 225"]
<tbody>[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]item #[/TD]
[TD]sales units[/TD]
[/TR]
[TR]
[TD="align: right"]January-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]February-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]March-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]April-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]May-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]June-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]July-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]August-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]September-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]October-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]November-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]December-12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]January-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]February-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]March-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]April-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]May-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]June-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]July-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]August-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]September-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]October-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]November-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]December-12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody><colgroup><col><col span="2"></colgroup>[/TABLE]



The measure I am trying to calculate is months of sale = sales units / on hand units *12

I can do this in dax and show the results in my pivot table no problem.

But what if I want to group the results?

I used a formula as follows to attach a grouping at the unit level

=if('Sales and COGS tel only'[MOS]<=3,"0-3",if('Sales and COGS tel only'[MOS]<=6,"3-6",if('Sales and COGS tel only'[MOS]<=9,"6-9",if('Sales and COGS tel only'[MOS]<=12,"9-12",if('Sales and COGS tel only'[MOS]<=18,"12-18",if('Sales and COGS tel only'[MOS]=9999,"No sale","24+"))))))

This works great on a power pivot by item. But I cant summarize the results by these groupings. That is becoause the context has to be at the item level for this calculation work

I am thinkin that in the power pivot I need to add a calculated measure as an added column to table 1. This should calculate months of sale for all items with the same item number.(ie lines 1 and 2 in tabel 1 both have item #1 in them - so my months of sale should use both these numbers to calculate the result). This can be summarized as follows

[TABLE="width: 520"]
<tbody>[TR]
[TD]Inventory table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]item[/TD]
[TD]Onhand [/TD]
[TD]Total Onhand for that item[/TD]
[TD]Sales[/TD]
[TD]MOS[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]93.6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]93.6[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]24[/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col><col span="2"></colgroup>[/TABLE]


Any thoughts are welcome

thanks
 
Upvote 0
Ahh i figured it out

Because I had the same item on many lines on both the inventory and sales table, there is a third table that I used to relates them together (a table with just a unique list of all item numbers). If I use the DAX formula there then I get the "months of sale" in the pivot window calculated at the item level. I can then roll that up in my pivot table and the calculation is "context free".
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Latest Weeks Volume Issue with DAX
Replies
2
Views
139
JEC
JEC
L
  • Solved
DAX: Double or Nested COUNTX() & SUMX()
Replies
6
Views
526
luuk
L
H
  • Question Question
Power Pivot - DAX code to dynamically calculate Run Rate in Excel based on Months Selected
Replies
3
Views
324
Matt Allington
M
K
  • Solved
DAX median in pivot table for an "almost empty" column
Replies
1
Views
212
Katchi
K
S
  • Question Question
DAX Expression Help
Replies
1
Views
152
RoryA
RoryA

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,645
Latest member
Tante

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top