Forum Discussion

Sachin_Tandon's avatar
Sachin_Tandon
Frequent Visitor
8 years ago
Solved

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...

 

 

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:

 

 

DAX Experts, Please help!

 

 

Here is the pbix file!

PBIX file

Need Help
  • Sachin_Tandon's avatar
    Sachin_Tandon
    8 years ago

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

     

    See attached:

     

    PBIX file 2

     

  • TomMartens's avatar
    TomMartens
    8 years ago

    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

12 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    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

      • Sachin_Tandon's avatar
        Sachin_Tandon
        Frequent Visitor
        8 years ago

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

         

        See attached:

         

        PBIX file 2

         

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    Hey,

     

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

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

     

     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:

     

    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

     

     

     

    • Sachin_Tandon's avatar
      Sachin_Tandon
      Frequent Visitor
      8 years ago

      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...