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