Forum Discussion

jcarville's avatar
jcarville
Skilled Sharer
8 years ago
Solved

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'...
Need Help
Show and Tell
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

Please explain the business question and show the expected result.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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] ))
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Ashish,

         

        Here is the PBIX   file link.

        Regards,

        Karthiga