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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sachin_Tandon
Frequent Visitor

Measure not displaying correct value in Total Row

Hi,

 

I'm truly stumped with this one.

 

I am trying to show the sales for different products, and brands, for different months. I have created measures, as I also want to be able to show the sales amounts against a certain reference or baseline month.

 

Now, one condition is that I need to be able to slice the pivot table by project status.

 

The measures work, except the Baseline Measures always show the total amounts, regardless of what the "project status" slicer is filtered on. So it will always show the same total amount, regardless of whether only cancelled projects are selected, or only active projects are selected (always only showing the amount as if both active and cancelled were selected), which I don't want.

 

Here are the picutres to show what is happening...

 

Picture1.pngPicture2.pngPicture3.png

 

Here is the DAX:

 

Total Sales = CALCULATE(SUM(Main_Data[Sales]))

 

 

Total Sales (Baseline) =
IF (
    ISBLANK ( [Total Sales] ),
    BLANK (),
    CALCULATE (
        SUM ( Main_Data[Sales] ),
        ALL ( Snapshot ),
        FILTER ( ALL ( Snapshot[Snapshot] ), Snapshot[Snapshot] = "Baseline" ),
        FILTER ( ALL ( Main_Data[Status] ), Main_Data[Status] = "Baseline" )
    )
)

 

 

Total Sales (Baseline)2 =
IF (
    ISFILTERED ( Main_Data[Project] ),
    [Total Sales (Baseline)],
    SUMX (
        VALUES(Main_Data[Role]),
        IF (
            ISBLANK ( [Total Sales] ),
            BLANK (),
            CALCULATE (
                SUM ( Main_Data[Sales] ),
                ALL ( Snapshot ),
                FILTER ( ALL ( Snapshot[Snapshot] ), Snapshot[Snapshot] = "Baseline" ),
                FILTER ( ALL ( Main_Data[Status] ), Main_Data[Status] = "Baseline" )
            )
        )
    )
)

 

Data looks like:

 

Screen Shot 2018-09-06 at 19.41.31.png

 

DAX Experts, Please help!

 

 

Here is the pbix file!

PBIX file

2 ACCEPTED SOLUTIONS

I tried creating a separate dimension table for Status, but now get completely bogus behaviour!

 

See attached:

 

PBIX file 2

 

Screen Shot 2018-09-06 at 21.32.20.pngScreen Shot 2018-09-06 at 21.32.29.png

View solution in original post

Perfect!

 

Please mark the most appropriate post as an answer, this will help others! And you might also consider to give that post a kudo, if it saved you headache and time 😉

 

Cheers,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

12 REPLIES 12
TomMartens
Super User
Super User

Hey,

 

I'm wondering if this will provide what you are looking for ...

My data model (I do not conisder the table DAX measures)

image.png

 

 My measure "Total Sales (Baseline)" looks like this

Total Sales (Baseline) = 
SUMX(
    VALUES(Main_Data[Project])
    ,IF(NOT(ISBLANK([Total Sales]))
    ,CALCULATE (
        SUM ( Main_Data[Sales] )
        ,ALL(Snapshot[Snapshot])
        ,ALL('Status'[Status])
        ,Snapshot[Snapshot] = "Baseline"
        ,'Status'[Status] = "Baseline"
    )
    ,BLANK()
    )
)

Using this measure the matrix looks like this:

image.png

 

Please be aware that the slicers are using the dedicated tables (dimension tables), and that I'm also using the status column from Status table in the matrix visual.

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks Tom,

 

This could be it!

 

Could you send back the pbix file. I've tried to replicate the results, but instead, now the total rows show, but the rows beneath come up as blank...

 

Screen Shot 2018-09-07 at 06.15.49.png

I think I solved it. I wasn't using the Status field from the Status slicer in the Matrix Table!

 

It seems to work now.

 

Thank you!

Perfect!

 

Please mark the most appropriate post as an answer, this will help others! And you might also consider to give that post a kudo, if it saved you headache and time 😉

 

Cheers,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom,

 

Here is my updated pbix file.

 

Can you see where I am going wrong?

 

PBIX file updated

 

KR,

 

Sachin

TomMartens
Super User
Super User

Hey,

 

please consider to provide a PBIX file that contains the sample data, upload the PBIX file to onedrive or dropbox and share the link.

 

By the way, it's never a good idea to use ALL in combination with the fact table where the numerical column resides that is used in the expression
'Main_Data'[Sales]

You should consider to create a separate dimension for the Status Column.

 

Regards,
Tom

 

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

As requested, I've uploaded the PBIX file

 

 

I tried creating a separate dimension table for Status, but now get completely bogus behaviour!

 

See attached:

 

PBIX file 2

 

Screen Shot 2018-09-06 at 21.32.20.pngScreen Shot 2018-09-06 at 21.32.29.png

Hey,

 

thanks for sharing the PBIX file, can you please upload the corresponding "Test Data.xlsx" also.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom,

 

Here is the raw data file...

 

XLS file

 

I've spent hours trying to solve this, but am just getting stumped...it must be a common problem for so many users!

Tried ALLSELECTED to ignore internal filters and keep external filters, but nothing seems to work... : (

 

Hopefully, the DAX Experts around can help me crack it!

I've set bi-directional filtering on between the Status Table and Main Sales Table which seems to help, but still trouble with the totals not adding up the rows beneath, when the status slicer is used

 

Sachin

Maybe I need a more sophisticated measure for the total rows...

 

Reading this... https://www.daxpatterns.com/parent-child-hierarchies/

 

But still scratching my head... : (

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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