March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi everyone,
I have been trying this for a while now and would really appreciate your help.
I am trying to calculate the effect of price increases for individual items per sales organization.
'Sales Price 19' and 'Sales Price 21' are measures calculated from the tables Net sales and Quantity.
The problem that I am having is that the pricing increase on each product does not add up to the total above.
Since not all items were sold by the same organization in both years, there are NaN values in the matrix.
I only want the pricing effect to be calculated if 'Sales Price 21' and 'Sales Price 19' are both >0. The pricing effect for sales org. 1 should be 1,69.
I have uploaded the dataset:
https://1drv.ms/u/s!AgksFfPmuAXgat3p1AIkxESCK1E?e=ZXGlGe
I'm kinda new with this, so please excuse if the solution is very obvious 😉
Thanks!
Solved! Go to Solution.
HI @Still_learning,
It sounds like a common measure total level calculated issue of multiple aggregations, you can take a look at Greg's blog if it suitable for your scenario:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
HI @Still_learning,
It sounds like a common measure total level calculated issue of multiple aggregations, you can take a look at Greg's blog if it suitable for your scenario:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
@Still_learning , try a measure like example
sumx(values(Table[sales org]), calculate(if( [Sales Price 21]> 0 && [Sales Price 19] >0,[Sales Price 21] -[Sales Price 19], blank())))
@amitchandak , thx for your help.
Unfortunately your measure does have the same result as my previous attempt.
P.S. I forgot to multiply the pricing difference with the quantity in my earlier screenshot so I changed that one
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
169 | |
144 | |
90 | |
70 | |
58 |