cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sfog
Advocate I
Advocate I

Blank result with SUMX & SUMMARIZE - where I have data in SAMEPERIODLASTYEAR but not current period

Hi

I have a DAX measure that is correct at the detail level but incorrect at the total level.

I created a new measure using SUMX and SUMAMRIZE to solve this – which gets me close but I still have one issue.

 

The issue I have is; the ‘Volume variance ($)’ measure below, is blank for rows where I do not have data in the current period - even though I have data in the previous period. Refer to cells marked as <No data?> below as an example of the issue.

 

In the example below, I do not have data for Product 3, in my current period. However, I do have data for Product 3 in the previous period – hence I am getting blank results for Product 3.

 

How can I change the Volume variance ($) measure to provide the ‘Revenue Variance’ for Product 3 in the 2 cells marked <No data?> below? 

 

Note:

  • I have a date slicer that determines the current period
  • Net Sales is the total sales for whatever the slicer is set for.
  • Prior Net sales, is: Net Sales for the ‘SAMEPERIODLASTYEAR’.
  • Revenue variance is: Net Sales – Prior Net Sales

 

The DAX measure that works at a detail level is:

Volume variance ($) Line Item =

    IF(SCP_Billing[Purchased 1 period] = TRUE(),

        [Revenue Variance],

        [Volume change (UoM)] * [Prior ASP]

    )

 

The DAX measure that does not work is:

Volume variance ($) =

            CALCULATE(

                SUMX(

                    SUMMARIZE(SCP_Billing,

                        SCP_Billing[SoldToStateOffice],

                        SCP_Billing[SoldToAccountManagerName],

                        SCP_Billing[MaterialProductHierarchy1Name],

                        SCP_Billing[MaterialProductHierarchy2Name],

                        SCP_Billing[SoldToPartyName]

                    ),

                        COALESCE(

                            [Volume variance ($) Line Item],

                            [Revenue Variance]

                        )  

                )           

            )

 

Here is an example of the data and the issue:

 

PH2

Manager

Customer

Prior Net Sales

Net Sales

Revenue Variance

Volume change (UoM)

Prior ASP

Purchased 1 period

Volume variance ($) Line

Volume Variance ($)

Status

Product 1

Account Manger

Customer 1

376401

123339

-253062

-290640

0.88

FALSE

-255763

-255763

Correct

Product 2

Account Manger

Customer 1

2480116

445928

-2034189

-1897236

1.08

FALSE

-2049015

-2049015

Correct

Product 3

Account Manger

Customer 1

1981221

 

-1981221

-1722801

1.15

TRUE

-1981221

<No data?>

Get blank values when there is no Product 3 data in the current period

Product 3

Account Manger

Customer 2

4329

 

-4329

-1604

2.7

TRUE

-4329

<No data?>

If there is no current or previous sales, it should just be equal to "Revenue Variance"

Product 4

Account Manger

Customer 1

 

1849

1849

432

 

TRUE

1849

1849

Correct
1 ACCEPTED SOLUTION
sfog
Advocate I
Advocate I

Hi

I managed to solve this. 

 

The root cause of the problem for me was, the "SUMMARIZE" portion of my calculation seemed to only include data related to my date slicer (which was from a date table with a relationship with my transaction data/fact table). So when I used any measure that used SAMEPERIODLASTYEAR, and there was data in the prior period, but not in the current period - I would not get prior period results (because of the date slicer & relationship).

 

So the solution was:

  • Remove the relationship between the date slicer and the transaction data (fact table). **Note: Strangely, even after I deleted the relationship, it somehow still filtered my data - so I had to physically delete the Date Table and re-create it 
  • Change every current/prior measure by removing time intelligence (SAMEPERIODLASTYEAR) and manually deriving the result e.g. PriorSales = CALCULATE(SUM([xx]), FILTER(Fact, Fact[Date] >= _PriorDateFrom && Fact[Date] <= _PriorDateTo

Once I removed the data table's relationship with the fact table, and manually derived all current/prior measures - my SUMX and SUMMARIZE measure worked as expected.

 

Here is the final measure.

 

Volume Variance ($) =
/* We need to use SUMX with SUMMARIZE in order to ensure the measure is calculated at a detail/line level and the results are sumamrized.
If we just report the measure below, it will be re-calclated at the total line level (context) and not reconcile with the sum of the indiviual lines */
SUMX(
ADDCOLUMNS(
SUMMARIZE(SCP_Billing,
SCP_Billing[SoldToStateOffice],
SCP_Billing[SoldToAccountManagerName],
SCP_Billing[MaterialProductHierarchy1Name],
SCP_Billing[MaterialProductHierarchy2Name],
SCP_Billing[SoldToPartyName]
),
"Volume Variance", /* Measure to calculate at the summarised level above */
IF(
SCP_Billing[Purchased 1 period] = TRUE(),
[Revenue Variance],
[Volume change (UoM)] * [Prior ASP]
)
),
[Volume Variance] /* Add the results of the measure calculated at the summarized level above i.e. ensure we do not re-calculate at the total line level (context) */

View solution in original post

8 REPLIES 8
sfog
Advocate I
Advocate I

Hi

I managed to solve this. 

 

The root cause of the problem for me was, the "SUMMARIZE" portion of my calculation seemed to only include data related to my date slicer (which was from a date table with a relationship with my transaction data/fact table). So when I used any measure that used SAMEPERIODLASTYEAR, and there was data in the prior period, but not in the current period - I would not get prior period results (because of the date slicer & relationship).

 

So the solution was:

  • Remove the relationship between the date slicer and the transaction data (fact table). **Note: Strangely, even after I deleted the relationship, it somehow still filtered my data - so I had to physically delete the Date Table and re-create it 
  • Change every current/prior measure by removing time intelligence (SAMEPERIODLASTYEAR) and manually deriving the result e.g. PriorSales = CALCULATE(SUM([xx]), FILTER(Fact, Fact[Date] >= _PriorDateFrom && Fact[Date] <= _PriorDateTo

Once I removed the data table's relationship with the fact table, and manually derived all current/prior measures - my SUMX and SUMMARIZE measure worked as expected.

 

Here is the final measure.

 

Volume Variance ($) =
/* We need to use SUMX with SUMMARIZE in order to ensure the measure is calculated at a detail/line level and the results are sumamrized.
If we just report the measure below, it will be re-calclated at the total line level (context) and not reconcile with the sum of the indiviual lines */
SUMX(
ADDCOLUMNS(
SUMMARIZE(SCP_Billing,
SCP_Billing[SoldToStateOffice],
SCP_Billing[SoldToAccountManagerName],
SCP_Billing[MaterialProductHierarchy1Name],
SCP_Billing[MaterialProductHierarchy2Name],
SCP_Billing[SoldToPartyName]
),
"Volume Variance", /* Measure to calculate at the summarised level above */
IF(
SCP_Billing[Purchased 1 period] = TRUE(),
[Revenue Variance],
[Volume change (UoM)] * [Prior ASP]
)
),
[Volume Variance] /* Add the results of the measure calculated at the summarized level above i.e. ensure we do not re-calculate at the total line level (context) */

View solution in original post

sfog
Advocate I
Advocate I

Perhaps it will help if I simplify the calculation? Basically, if I use the calculaion below (which is the same as the original post - just simplified) - I will only get values for [Current Net Sales]. I will not get any data for [Prior Net Sales] although I can see there are 2 rows for [Prior Net Sales] - when I report [Prior Net Sales] in the table.

 

If this measure could return Prior net sales (if any) else current net sales - I could get my original measure to work.

Regards 

 

 

CALCULATE(
SUMX(
SUMMARIZE(
SCP_Billing,
SCP_Billing[SoldToStateOffice],
SCP_Billing[SoldToAccountManagerName],
SCP_Billing[MaterialProductHierarchy1Name],
SCP_Billing[MaterialProductHierarchy2Name],
SCP_Billing[SoldToPartyName]
),
IF(SCP_Billing[Current Net Sales] = 0 || SCP_Billing[Prior Net Sales] = 0,
SCP_Billing[Prior Net Sales],
SCP_Billing[Current Net Sales]
)
)
)
smpa01
Super User
Super User

@sfog does this help?

_measure =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            SCP_Billing,
            SCP_Billing[SoldToStateOffice],
            SCP_Billing[SoldToAccountManagerName],
            SCP_Billing[MaterialProductHierarchy1Name],
            SCP_Billing[MaterialProductHierarchy2Name],
            SCP_Billing[SoldToPartyName]
        ),
        "test",
            COALESCE (
                IF (
                    SCP_Billing[Purchased 1 period] = TRUE (),
                    [Revenue Variance],
                    [Volume change (UoM)] * [Prior ASP]
                ),
                [Revenue Variance]
            )
    ),
    [test]
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Hi @smpa01 

No, it produces the same result i.e. I am not getting results for the prior period.

Thanks for looking into this.

Regards 

m3tr01d
Responsive Resident
Responsive Resident

@sfog I think it would help us to give the code for these measures 

[Purchased 1 period],
[Revenue Variance],
[Volume change (UoM)],
[Prior ASP]

Also, the easiest way to help you would be to give us a sample PBix file but I assume this must be confidential data.

Here you go @m3tr01d

 

Purchased 1 period =
/* if either the current or prior period's sales are zero - return TRUE */
IF(SCP_Billing[Net Sales] = 0 || SCP_Billing[Prior Net Sales] = 0,
TRUE(),
FALSE()
)
 
Revenue Variance =
/* Current minus prior period net sales*/
SUM(SCP_Billing[InvoicedSalesNetAfterRebates]) - SCP_Billing[Prior Net Sales]
 
Volume change (UoM) =
/* Current minus prior period volume*/
SUM(SCP_Billing[InvoicedSalesRUoMQty]) - SCP_Billing[Prior Volume]
 
Prior ASP =
Divide(
SCP_Billing[Prior Net Sales], SCP_Billing[Prior Volume]
)
 
Yes, you are right - unfortunately the data is too sensitive to share the pbix file. 
 
Regards 
lbendlin
Super User
Super User

@sfog Whoa. "CALCULATE(SUMX(SUMMARIZE(..."  I wish there was an easy way to point out all the context transitions going on here.

 

You are not showing the expected result at the total level - can you please add that to your sample data?

 

@lbendlin the data is very sensitive. I can skew the results but all I would be showing is an incorrect total of 5 detail rows - not sure it will add much value. The incorrect total is the common and expected result when using DAX i.e. calculating a % or a rate at a detail level works, but at the total level level it is re-calculate in the conext of the total line - nothing new or special here.   

 

Hope that's okay and thanks for looking into this.

 

Regards 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors