Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MIkkelHyldig
Helper II
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 NumberRetail Sales Amount LCYRetail Sales Amount LCY LYIndex
1100100100
2400200200
3300  
Total600300200 (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
MIkkelHyldig
Helper II
Helper II

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

Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.