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
jcarville
Skilled Sharer
Skilled Sharer

Incorrect Measure Total

Hi,

 

I am getting the incorrect total for my measure '% Turnover Value'. The total is showing 135,656, but I expect a figure of 69,089.

I am presuming the Accounts with no 'Average of % Turnover' are effecting the outcome but not sure of how to fix this.

 

I know the total for 'Sales Value Final £/€' is also incorrect but I presume its the same issue.

 

ResultsResults'% Turnover Value' Measure'% Turnover Value' Measure

 

Can anybody help please?

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

The SUMX() portion of the formula should be something like this

 

=SUMX(SUMMARIZE(VALUES(Rebates[Account/Group]),[Account/Group],"ABCD",SUM(Sales value final)*AVERAGE(Rebates[% turnover])),[ABCD])

 

Include the curency symbol in the underlines portion.

 

If this formula does not help, then share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

23 REPLIES 23
Anonymous
Not applicable

Hi,

 

I am expecting for  the "Total" to display the sum of the values in the column. Can anyone help me with this please?

 

Formula is, 

Zero availability =
var A = SUMX (
agg_bb_sku_oos_daily,
CALCULATE (
DISTINCTCOUNT ( agg_bb_sku_oos_daily[source_sku_id] ),
agg_bb_sku_oos_daily[oos_in_hours_range] = "Zero Availability"
))
var B = CALCULATE(DISTINCTCOUNT(agg_bb_sku_oos_daily[sku_oos_date]),GROUPBY(agg_bb_sku_oos_daily,agg_bb_sku_oos_daily[source_sku_id]),agg_bb_sku_oos_daily[sku_oos_date])
return
A/B

 
source_sku_idsku_oos_dateoos_in_hours_rangeVolumeZero AvailabilityVar1Var2
SKU11/8/2019Zero Availability100.00111
SKU21/8/2019test1309.000 6
SKU21/9/2019Zero Availability138.000.16666666716
SKU21/10/2019Zero Availability20941.000.16666666716
SKU21/11/2019Zero Availability214.000.16666666716
SKU21/12/2019Zero Availability234.000.16666666716
SKU21/13/2019Zero Availability294.000.16666666716
Total   166

 

Hi,

Please explain the business question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish,

 

We have a measure "Zero Availability" which gets calculate using logic (table data provided in above comments),  This returns row-wise correct values but at the total we would expect to see summation of row values. As shown in previous comments DAX will do again calculation (row context) and returning result as "1"  but what we expect is "1.8333" which  is row values SUM.

Var 1 = SUMX ( agg_bb_sku_oos_daily, CALCULATE ( DISTINCTCOUNT ( agg_bb_sku_oos_daily[source_sku_id] ), agg_bb_sku_oos_daily[oos_in_hours_range] = "Zero Availability" ) ),
Var 2 = CALCULATE ( DISTINCTCOUNT ( agg_bb_sku_oos_daily[sku_oos_date] ), GROUPBY ( agg_bb_sku_oos_daily, agg_bb_sku_oos_daily[source_sku_id] ),  agg_bb_sku_oos_daily[sku_oos_date]  ))
Zero Availability = Var 1/ Var 2
 
I have used below formula to get the correct results at the "Total" but, Performance is degraded as it is iterating for each rows. Is there any alternative approach which doesn't impact performance as well?
RowValues =  DIVIDE (SUMX ( agg_bb_sku_oos_daily, CALCULATE ( DISTINCTCOUNT ( agg_bb_sku_oos_daily[source_sku_id] ), agg_bb_sku_oos_daily[oos_in_hours_range] = "Zero Availability" ) ),
, CALCULATE ( DISTINCTCOUNT ( agg_bb_sku_oos_daily[sku_oos_date] ), GROUPBY ( agg_bb_sku_oos_daily, agg_bb_sku_oos_daily[source_sku_id] ),  agg_bb_sku_oos_daily[sku_oos_date]  )))
Zero Availability = VAR __table =SUMMARIZE ( 'agg_bb_sku_oos_daily', [source_sku_id], "__value", [RowValues] )
RETURN
IF (HASONEVALUE ( agg_bb_sku_oos_daily[source_sku_id] ),[RowValues],SUMX( __table, [__value] ))

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish,

 

Here is the PBIX   file link.

Regards,

Karthiga 

Hi,

That takes me to a sign-in page.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

 

The SUMX() portion of the formula should be something like this

 

=SUMX(SUMMARIZE(VALUES(Rebates[Account/Group]),[Account/Group],"ABCD",SUM(Sales value final)*AVERAGE(Rebates[% turnover])),[ABCD])

 

Include the curency symbol in the underlines portion.

 

If this formula does not help, then share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur Great solution that worked perfectly for me. Thank you for the solution!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

I have a problem with your solution.

 

I have an excel file with 3 tables:

a) Fact table with sales transactions

b) Dim table with Customer data

c) Dim table with Sales Personnel data

 

I'm trying to calculate the commissions per Sales Representative, but in this example, I'm just following an excercise to calculate the excess of $1,000 USD and have a total amount based on this measure. 

 

My measure, which works perfectly fine for row level but not for the total, is the following:

Excess = 
VAR ExcessAmount = SUM( Sales[Amount] ) - 1000
VAR ExcessTotal = SUMMARIZE( Sales, Slp[Slpcode], "ExcessTot", ExcessAmount )
RETURN
IF(
    HASONEFILTER( Slp[Slpcode] ),
    IF(
        SUM( Sales[Amount] ) < 1000,
        0,
        ExcessAmount
    ),
    SUMX(
        VALUES( Slp[Slpcode] ),
        ExcessAmount
    )
)

Here's my pbix file also.

 

total_error.png

The total shouls be $6,300

 

I'd really appreaciate your help, and the Community.

 

Best regards,

 

Fernando

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur ,

 

Great! Thank you very much Ash!

It's like magic.

 

Cheers... Fernando

 

You are welcome.  If my reply helped, please Accept it as Solution.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur ,

 

Sure it did! I would if I knew how!

As it was an already solved question I don't find where to mark it as solved. Please show me.

Thanks again.

Fernando

 

calerof
Impactful Individual
Impactful Individual

Hi @Ashish_Mathur ,

 

I'm running into problems with my actual data model. The data I posted here was a dummy for the purpose of the excercise. In my code I'm using variables and still don't get the correct total amount for the measure.

 

Comision = 
VAR Sales= [Sales Total]
VAR Margin = [Margin Total]
VAR Commission = 
    SWITCH( TRUE(),
        Sales >= 200000 && Sales < 250000, 0,
        Sales>= 250000 && Margin >= 0.52 && Margin < 0.56, 0.01,
        Sales>= 250000 && Margin < 0.60, 0.015,
        Sales>= 250000 && Margin >= 0.6, 0.02, 
    0
    )
VAR BaseBonus = 
    SWITCH( TRUE(),
        Sales>= 200000 && Sales < 250000, 1000,
        0
    )
VAR CommissionAmount = Sales * Commission + BaseBonus
VAR ComisTotal = SUMMARIZE( VALUES( OCRD[SlpCode] ), OCRD[SlpCode], "ComisTot",  MAX( 0, CommissionAmount ) )
RETURN
SUMX( ComisTotal, [ComisTot] )

 

This is the result I get with the measure above:

 

error_total_measure_w_var_original_model.png

 

I did the same trick with the excercise to try to understand what's wrong with my measure, and modified yours as follows:

Excess2 = 
VAR TotalAmount = SUM( Sales[Amount] )
VAR ExcessAmount = TotalAmount - 1000
VAR ExcessTotal = SUMMARIZE(
                    VALUES( Customer[Slpcode] ), Customer[Slpcode], "ABCD", MAX( 0, ExcessAmount )
                )
RETURN
SUMX(ExcessTotal,[ABCD])

 

Which follows the same logic as your proposed solution, and that worked correctly, but not with these modifications.

 

error_total_measure_w_var.png

 

Here's the pbix file.

 

Do you know why? Could you please help?

 

I appreciate your help a lot.

 

Best regards,

 

Fernando

 

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

 

Sorry, I added the link to the pbix file.

 

Thanks!

 

F

 

Hi,

I do not work with variables so i cannot help.  What problem do you face when you apply my formula on your actual dataset.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Im at a breaking point, tried all the suggestions from many articles but still struggling with fixing totals.

1. created a measure to return sales last year using the below

VAR = CALCULATE(SUMX(Sales,Sales[revenue local currency]),DATEADD('Calendar'[Date],-1,YEAR))
 
2. Created another measure to return fx rate from the sames for the selected year and month on the slicer
 
VAR B =MIN(Sales[rate])
 
3. convert the local currency revenue to USD
DIVIDE(A,B,0)

Calculation works, but the total is incorrect. I used the below DAX in one measure,
 
REV_USD_LY = 
VAR A = CALCULATE(SUMX(Sales,Sales[revenue local currency]),DATEADD('Calendar'[Date],-1,YEAR)) 
VAR B = CALCULATE(MIN(Sales[rate]),ALL('Calendar'))
VAR div = DIVIDE(A,B,0)
Captured.PNG
 
If you see the total, it returns sames as revenue local currency last year, can you please help?

I'm having a similar struggle  where within my datagrid individuals rows are calculating but the total line is wrong.

 

Stuck thoughts?

 

Here is my Dax expression:

 

MTD_PY_Variance_Matrix =
 (
    CALCULATE (
        KPI_Finance_Matrix[MTD_Actuals_Matrix] - KPI_Finance_Matrix[MTD_PY_Actuals],
        KPI_Finance_Matrix[Group] = "Revenue"
    )
)
    + (
        CALCULATE (
            KPI_Finance_Matrix[MTD_Actuals_Matrix] - KPI_Finance_Matrix[MTD_PY_Actuals],
            KPI_Finance_Matrix[Group] = "Gross Margin"
        )
    )
    + (
        CALCULATE (
            KPI_Finance_Matrix[MTD_PY_Actuals] - KPI_Finance_Matrix[MTD_Actuals_Matrix],
            KPI_Finance_Matrix[Group] = "Operating Expenses"
        )
    )

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.