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 Community,
I need help with this measure. I got a dataset, with Items sold(ItemID), with quantity(quantity) sold for each item.
we also got a weightage(product weightage) of each itemID.
I am trying to calculate a mix indicator for all these itemIDs based upon the QTY sold and the weightage for each item.
I am calculating the values based on following formula
mix indicator calculted = VAR item_sum = SUM ( 'dump data test01'[Quantity] ) VAR total_sum = CALCULATE ( SUM ('dump data test01'[Quantity] ), ALL ( 'dump data test01'[ItemID] ) ) var result = DIVIDE ( item_sum, total_sum )*SUM('dump data test01'[product weightage]) RETURN result
This gives me a value, which is working fine on row level. Though on the totals line, it is again calculating the mix. I on the other hand, want the total to show the sum of mix for all values, which in this example should be around 15% and not 2923%.
Report attached here with sample data and formula.
Any direction in this regard would be a great help.
Regards
emudria
Solved! Go to Solution.
Hi @Anonymous
You can add a new measure:
Measure 2 = SUMX(VALUES('dump data test01'[ItemID]),[mix indicator calculted])
Best regards,
Dina Ye
Hi @Anonymous
You can add a new measure:
Measure 2 = SUMX(VALUES('dump data test01'[ItemID]),[mix indicator calculted])
Best regards,
Dina Ye
Hi , If i want add more columns in that measure how should i do?
what if I have another field along with item such as "X", will this formula still work?
THANK YOU so much Dina. works perfectly. I had spent so many hours trying to do that. Thanks again.
Can I please ask two things:
1 - what is happening in this formula
2 - why doesnt it work if instead of creating a new measure, I put the returned variable 'result' in this formula in same measure
ie
measure =
SUMX(VALUES('dump data test01'[ItemID]),result)
awesome
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 |