Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to change how my measure calculates the total. For that I'm using the HASONEFILTER function and that works just fine, it correctly evaluates the first expression for each value row and total expression for the total row, but for the total row I just can't get the result right. Basically what I'd like to do is instead of evaluating the expression on the totals, I want to see the expression evaluated for each row and then summed up. To my understanding that is what SUMX is for, but it is somehow not correct.
What am I doing wrong?
Measure_test =
switch(true(),
hasonefilter(master_customer_database[Market]),
divide(sum(table1[column1]), sum(table1[column2])),
sumx(table1, divide(sum(table1[column1]), sum(table1[column2])))
)
Solved! Go to Solution.
Try something like this for the total:
Measure2 =
SUMX(
SUMMARIZE(
'Table';
'Table'[Category];
"Value" ; [Measure]
) ;
[Value]
)
Br,
J
Try the following (I'm using random data):
1) the measures:
Sum of Actuals = SUM('Data Table'[Actuals])
Sum of Target = SUM('Data Table'[Target])
Actuals / Target = DIVIDE([Sum of Actuals]; [Sum of Target])
SUMX of Actuals / Target = SUMX(SUMMARIZE('Data Table'; 'Data Table'[Channel];
"Calculation"; [Actuals / Target]);
[Calculation])
And you will get this result
Proud to be a Super User!
Paul on Linkedin.
share sample data and expect output
Category | Value1 | Value2 | Measure (Value1/Value2) |
Cat1 | 1778470 | 217976 | 8.16 |
Cat2 | 2496634 | 132294 | 18.87 |
Total | 4275104 | 350270 | 24.41 |
So as you can see Measure is Value1 / Value 2 for each row. The Total Value 1 and Total Value 2 is also correct.
I noticed now that the 24.41 is the double of what the Total Value1 / Total Value2 would be (12.205). But I don't see why.
Expected result would be 8.16+18.87=27.03
Try something like this for the total:
Measure2 =
SUMX(
SUMMARIZE(
'Table';
'Table'[Category];
"Value" ; [Measure]
) ;
[Value]
)
Br,
J
It works, thank you all!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |