Iterating sum over dimension table

[dicken]

Hi,
I have a question regarding the above so;
I have a simple formula in measure;

  over dim table:=SUMX('Product','Product'[Price]*CALCULATE(SUM(Sales[sales])))

But I also have a customer table with there discounts, now if iterating over the sales table I would just use.

Total Revenue:=SUMX(Sales,Sales[sales]*RELATED('Product'[Price])*(1-RELATED(Customer[Discount])))

But so far I've been unable to use the dicount column in the first example,

Any suggestions, pointers, gratefully received.

Richard

 

[Matt Allington]

Option 2 will work, but is generally regarded as inefficient. You could generate a virtual table containing all the combinations of discount and price, and iterate over that. Something like this.

Total Revenue :=
SUMX (
SUMMARIZE ( Sales, 'Product'[Price], Customer[Discount] ),
'Product'[Price] * ( 1 - Customer[Discount] )
* CALCULATE ( SUM ( Sales[sales] ) )
)

The summarize line generates a virtual dim table containing all the unique combinations of product price and cust discount.

 

[dicken]

Option 2 will work, but is generally regarded as inefficient. You could generate a virtual table containing all the combinations of discount and price, and iterate over that. Something like this.

Total Revenue :=
SUMX (
SUMMARIZE ( Sales, 'Product'[Price], Customer[Discount] ),
'Product'[Price] * ( 1 - Customer[Discount] )
* CALCULATE ( SUM ( Sales[sales] ) )
)

The summarize line generates a virtual dim table containing all the unique combinations of product price and cust discount.

I haven't had a chance to try this out but I'll look into it, I've not used SUMMARIZE, thanks for the help.
Oh, and I was have a re read of 'Context transition revisited' while doing the washing this morning.
RD

 

[dicken]

Option 2 will work, but is generally regarded as inefficient. You could generate a virtual table containing all the combinations of discount and price, and iterate over that. Something like this.

Total Revenue :=
SUMX (
SUMMARIZE ( Sales, 'Product'[Price], Customer[Discount] ),
'Product'[Price] * ( 1 - Customer[Discount] )
* CALCULATE ( SUM ( Sales[sales] ) )
)

The summarize line generates a virtual dim table containing all the unique combinations of product price and cust discount.

Hi Matt,

Well I made slight change, in that I added an 'applied discount column' ( discount 5% so 95%) and then ran this


New try :=
SUMX (
SUMMARIZE ( Sales, Customer[Applied Discount], 'Product'[Price] ),
'Product'[Price] * Customer[Applied Discount]
* CALCULATE ( SUM ( Sales[sales] ) )
)
I've been putting various summarized 'stuff"' in to DAX studio, don't understand it, but know more than I did a couple of days ago.
Thanks,
Richard.

 

[Matt Allington]

That's great. Summarize simply creates a table. Here are the rules.
1. start from the fact table (use fact as the first parameter)
2. add any columns you want to include in the new table

Summarize is different from ALL in that it can include columns from more than 1 table, as long as the tables are joined via a common fact table

 

[dicken]

That's great. Summarize simply creates a table. Here are the rules.
1. start from the fact table (use fact as the first parameter)
2. add any columns you want to include in the new table

Summarize is different from ALL in that it can include columns from more than 1 table, as long as the tables are joined via a common fact table

I've been experimenting and found that Crossjoin also works, is Summarize more efficient as I take it that it returns a smaller table to iterate over, as I work on small homemade tables, ( it's easier to spot where / when errors occur) I can't tell.
Any assistance welcome ;

=
SUMX (
CROSSJOIN ( ALL ( 'Product'[Price] ), ALL ( Customer[discoiunt] ) ),
'Product'[Price] * Customer[discoiunt]
* CALCULATE ( SUM ( sales[Sales] ) )
)

Richard