Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Can anybody help please?
Solved! Go to Solution.
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.
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_id | sku_oos_date | oos_in_hours_range | Volume | Zero Availability | Var1 | Var2 |
SKU1 | 1/8/2019 | Zero Availability | 100.00 | 1 | 1 | 1 |
SKU2 | 1/8/2019 | test | 1309.00 | 0 | 6 | |
SKU2 | 1/9/2019 | Zero Availability | 138.00 | 0.166666667 | 1 | 6 |
SKU2 | 1/10/2019 | Zero Availability | 20941.00 | 0.166666667 | 1 | 6 |
SKU2 | 1/11/2019 | Zero Availability | 214.00 | 0.166666667 | 1 | 6 |
SKU2 | 1/12/2019 | Zero Availability | 234.00 | 0.166666667 | 1 | 6 |
SKU2 | 1/13/2019 | Zero Availability | 294.00 | 0.166666667 | 1 | 6 |
Total | 1 | 6 | 6 |
Hi,
Please explain the business question and show the expected result.
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.
Hi,
Share the link from where i can download your PBI file.
Hi,
That takes me to a sign-in page.
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.
@Ashish_Mathur Great solution that worked perfectly for me. Thank you for the solution!
You are welcome.
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.
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.
You are welcome. If my reply helped, please Accept it as Solution.
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
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:
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.
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.
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.
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
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)
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"
)
)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |