Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi
I have a table where I wish to calculate the index (this year compared to last year) for the total correctly with a measure where it returns 200 at the moment. I want the measure to return 167:
Store Number | Retail Sales Amount LCY | Retail Sales Amount LCY LY | Index |
1 | 100 | 100 | 100 |
2 | 400 | 200 | 200 |
3 | 300 | ||
Total | 600 | 300 | 200 (Incorrectly) - should be 167 |
Currently Im using the following measure which returns the above incorrect result :
New All Store Sales LCY Index =
SWITCH (
TRUE (),
DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100 < 2000
&& DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100 > -100,
DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100,
BLANK ()
)
I tried to solved it by using this measure but get an error;
New All Store Sales LCY Index =
IF(HASONEFILTER('dm DimStore'[Store Number]),
IF(
SWITCH (
TRUE (),
DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100 < 2000
&& DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100 > -100,
DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100,
BLANK ()),
0,
SWITCH (
TRUE (),
DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100 < 2000
&& DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100 > -100,
DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100,
BLANK ()
)),
FILTER(
'dm DimStore',
SWITCH (
TRUE (),
DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100 < 2000
&& DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100 > -100,
DIVIDE ( [Retail Sales Amount Incl VAT LCY], [New All Store Sales LCY LY] ) * 100,
BLANK ()
)))
Best regards,
Mikkel
Thanks for the answer, @Anonymous
I tried this measure but it returns blank in total:
New Sales L4L Index =
VAR __table =
SUMMARIZE (
'dm FactRetailSales',
'dm DimStore'[Store Number],
"__value",
SWITCH (
TRUE (),
DIVIDE ( [New L4L Sales LCY], [New L4L Sales LY LCY] ) * 100 < 2000
&& DIVIDE ( [New L4L Sales LCY], [New L4L Sales LY LCY] ) * 100 > -100, DIVIDE ( [New L4L Sales LCY], [New L4L Sales LY LCY] ) * 100,
BLANK ()
)
)
RETURN
IF (
HASONEVALUE ( 'dm DimStore'[Store Number] ),
SWITCH (
TRUE (),
DIVIDE ( [New L4L Sales LCY], [New L4L Sales LY LCY] ) * 100 < 2000
&& DIVIDE ( [New L4L Sales LCY], [New L4L Sales LY LCY] ) * 100 > -100, DIVIDE ( [New L4L Sales LCY], [New L4L Sales LY LCY] ) * 100,
BLANK ()
),
SUMX ( __table, [__value] )
)
HI @MIkkelHyldig,
According to your description, it sounds like a common measure total level calculation issue. You can refer to Greg's blog to know how to handle this scenario:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
User | Count |
---|---|
74 | |
71 | |
42 | |
31 | |
28 |
User | Count |
---|---|
99 | |
93 | |
50 | |
49 | |
46 |