cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

## Measure to solve wrong total (row context)

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

2 REPLIES 2
Helper II

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] )
)

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors