Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a simple table. I am trying to find the absolute difference between two columns
For the measure column the total is not correct, it is showing the difference between the total of columns not the total of the measure
At the moment the formula for the measure is "Measure = abs(SUM(Sheet1[Qty]) - sum(CROSSTAB[M3]))"
I have looked through all the previous solutions to this problem and i came up with the below formula
"
Measure = if(isfiltered('Product Key'[Product Key - KPI]), abs(SUM(Sheet1[Qty]) - sum(CROSSTAB[M3])), sumx(......"
I am stuck with the sumx part of the formula. I do not know how to finish off the sumx part as column "m3" & "qty" are from different tables in the report
Thank you for your help
Hi,
I desperately need help in creating a dax expression.
First of all due to proprietary issue, I wont be able to share any data, but only a dummy data set.
I have a data set like this:
Company KPI1 KPI2
A 2% 3000
B 1.8% 2500
C 2% 4000
I need to calculate weighted average so that will get the result as
((KPI1 of A * KPI2 of A) + (KPI1 of B * KPI2 of B) + (KPI1 of C * KPI2 of C))/(KPI2 of A + KPI2 of B + KPI2 of C)
The challenge is the data is in different related tables and I would have to put a Slicer, whereby the same is calulated either for all the companies (A, B, C) or any one or more of them.
Any help from you guys would be be very helpful.
Thanks,
Hi LasyaJ,
To be general, sumx or calculate function can be implemented on two different tables if they have relationship with each other like pattern below:
Result = SUMX ( FILTER ( Table1, Table1[Column1] = Condition1 ), Table2[Column2] ) Result = CALCULATE ( SUM ( Table2[Column2] ), FILTER ( Table1, Table1[Column1] = Condition1 ) )
Regards,
Jimmy Tao
Hi Jimmy,
Thanks for the reply
sorry if this is a dumb question but what is Condition1?
Do I add "= Condition1" in my formula?
I changed it to the below but now I get a blank for my total
Measure = if(isfiltered('Product Key'[Product Key - KPI]), abs(SUM(Sheet1[Qty]) - sum(CROSSTAB[M3])), CALCULATE(sum(CROSSTAB[M3]), FILTER(Sheet1,Sheet1[Qty])))
Thanks
Hi LasyaJ,
You should add some conditions based on your requirement such as:
CALCULATE(sum(CROSSTAB[M3]), FILTER(Sheet1,Sheet1[Qty] >= 1)))
Regards,
Jimmy Tao
Hi Jimmy,
Sorry I am a real newbie at this...
I am still getting a blank for my total in the measure column
Measure = if(isfiltered('Product Key'[Product Key - KPI]), abs(SUM(Sheet1[Qty]) - sum(CROSSTAB[M3])), CALCULATE(sum(CROSSTAB[M3]), FILTER(Sheet1, Sheet1[Qty] >=1)))
Could it be a problem with my data or is the formula I'm doing all wrong?
Thanks
Hi LasyaJ,
Have you created a relationship between 'Sheet1' and 'CROSSTAB', you should have relationship first before you call columns from other table.
Regards,
JImmy tao
Hi Jimmy,
There is no direct relationship between 'Sheet1' and 'CROSSTAB'
They are two different data sets. The only way they are linked is through a lookup table which has a common value
Hi LasyaJ ,
I think you mean "from different data source ", not "from different data set", right? So could you create relationship between 'Sheet1' and your lookuptable, ''CROSSTAB" and your lookuptable.
Regards,
Jimmy Tao
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |