Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello... I have an issue.
I want to calculate a linear regression. The thing is that my data is in the same column, and I have not found the way to do that.
I have something like this:
ID | Nombre de la variable | Opinion |
1 | Wine | 1 |
1 | Meat | 2 |
1 | Vegetables | 3 |
1 | Cereal | 4 |
1 | Candy | 5 |
1 | Chicken | 6 |
2 | Wine | 5 |
2 | Meat | 4 |
2 | Vegetables | 3 |
2 | Cereal | 2 |
2 | Candy | 1 |
2 | Chicken | 6 |
3 | Wine | 4 |
3 | Meat | 3 |
3 | Vegetables | 2 |
3 | Cereal | 1 |
3 | Candy | 6 |
3 | Chicken | 5 |
4 | Wine | 3 |
4 | Meat | 2 |
4 | Vegetables | 1 |
4 | Cereal | 6 |
4 | Candy | 5 |
4 | Chicken | 4 |
5 | Wine | 2 |
5 | Meat | 1 |
5 | Vegetables | 6 |
5 | Cereal | 5 |
5 | Candy | 4 |
5 | Chicken | 3 |
6 | Wine | 1 |
6 | Meat | 6 |
6 | Vegetables | 5 |
6 | Cereal | 4 |
6 | Candy | 3 |
6 | Chicken | 2 |
7 | Wine | 1 |
7 | Meat | 2 |
7 | Vegetables | 3 |
7 | Cereal | 4 |
7 | Candy | 5 |
7 | Chicken | 6 |
One part of the formula is sumx(XY), where "Y" is the opinion if "wine".
"X" have to variate among the opinion of "meat", "chicken", "candy", etc. So "X" have to be like a filter.
I really need help.
i.e.: Y= Wine ; X = Chicken
ID = 1: 1*6 = 6
ID = 2: 5*6 = 30
ID = 3 : 4*5 = 20
ID = 4: 3*4 = 12
ID = 5: 2*3 = 6
ID = 6: 1*2 = 2
ID = 7: 1*6 = 6
total = 82
I need that in a DAX formula
Solved! Go to Solution.
One way to do it:
Measure 12 = VAR __table = SUMMARIZE(ALL('Table12'),[ID]) VAR __table1 = ADDCOLUMNS(__table,"__y",SUMX(FILTER(ALL('Table12'),[ID]=EARLIER([ID]) && [Nombre de la variable]="Wine"),[Opinion]),"__x",SUMX(FILTER(ALL('Table12'),[ID] = EARLIER([ID]) && [Nombre de la variable]="Chicken"),[Opinion])) VAR __table2 = ADDCOLUMNS(__table1,"__product",[__x]*[__y]) RETURN SUMX(__table2,[__product])
One way to do it:
Measure 12 = VAR __table = SUMMARIZE(ALL('Table12'),[ID]) VAR __table1 = ADDCOLUMNS(__table,"__y",SUMX(FILTER(ALL('Table12'),[ID]=EARLIER([ID]) && [Nombre de la variable]="Wine"),[Opinion]),"__x",SUMX(FILTER(ALL('Table12'),[ID] = EARLIER([ID]) && [Nombre de la variable]="Chicken"),[Opinion])) VAR __table2 = ADDCOLUMNS(__table1,"__product",[__x]*[__y]) RETURN SUMX(__table2,[__product])
Really thanks man..!! It was usefull... Proud to be a datanaut.!
You gave me this formula:
DIMENTION | Code | SubDimention | GeneralQ |
Food | F1 | Image | |
Food | F2 | Image | |
Food | F3 | Image | |
Food | F4 | Image | |
Food | F5 | Image | |
Food | F6 | Image | |
Food | F0 | ImageGeneral | 1 |
Grocery | G1 | Power | |
Grocery | G2 | Power | |
Grocery | G3 | Power | |
Grocery | G4 | Power | |
Grocery | G5 | Power | |
Grocery | G6 | Power | |
Grocery | G0 | PowerGeneral | 1 |
Lexury | L1 | Cereal | |
Lexury | L2 | Cereal | |
Lexury | L3 | Cereal | |
Lexury | L4 | Cereal | |
Lexury | L5 | Cereal | |
Lexury | L6 | Cereal | |
Lexury | L0 | CerealGeneral | 1 |
Where "Chiken", "Meat", "Candy", etc, are my principal questions (General Questions).
So, in the part of the formula
[Nombre de la variable] = "Chicken", I need to change with a filter in the sheet, the option.
I have tryed with HASONVALUE, SELECTED VALUE, RELATED[Nombre de la variable] = TableN[Code], and nothing happened
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |