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.
I have created a calculated field which is a combined filed of 2 already existing fields. The formula I have used is :
Combined Units = if(ISBLANK(SUM(accounts[totalvotes])), SUM(accounts[units]), SUM(accounts[totalvotes]))
But the sum of combined units is not correct. Screen-shot attached.
Solved! Go to Solution.
Hi @poojik,
I'm assuming that you are using a measure, in this case the calculations are made taking into account the context of the formula, so when you get to the total it's also calculated in that way and not the sum of the previous values, you need to place that value around aggregator.
Create a second measure with the following code to use on your table:
Measure 2 = IF ( HASONEFILTER ( Table[Name] ); [Combined Units]; SUMX ( Table; Combined Units] ) )
For better performance change the Table in the SUMX by ALL( Colum 1 , column 2, ...) and choose all the columns need for grouping/sorting instead of placing the all table.
Ths formula calculate the measure on every single row, and on the total since it has no value it sums the previous rows.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI @poojik,
When you do a measure it's calculated based on context, so the formula that you have is calculating the division by the current row it will give you 1, you need to something like this:
One third of Unit Count = IF ( accounts[Combined Units] < CALCULATE ( ( SUM ( accounts[Unit Count] ) / 3 ), ALL ( Accounts[Unit Count] ) ), "Less than one thrid", "Greater than one third" )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @poojik,
I'm assuming that you are using a measure, in this case the calculations are made taking into account the context of the formula, so when you get to the total it's also calculated in that way and not the sum of the previous values, you need to place that value around aggregator.
Create a second measure with the following code to use on your table:
Measure 2 = IF ( HASONEFILTER ( Table[Name] ); [Combined Units]; SUMX ( Table; Combined Units] ) )
For better performance change the Table in the SUMX by ALL( Colum 1 , column 2, ...) and choose all the columns need for grouping/sorting instead of placing the all table.
Ths formula calculate the measure on every single row, and on the total since it has no value it sums the previous rows.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you very much @MFelix this works. But logically I don't understand the logic behind HASONEFILTER(Table[Name]) .
Hi @poojik,
The HASONEFILTER check if in the current context (line) there is a name if it exists a name then it return the measure, if there is no name total or subtotal rows then it make the sum of the previous lines SUMX
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @MFelix ! Makes sense.
Once the sum is calculated I am trying to filter all those accounts with "Combined units" less than one third of "unit count". To do that I have created another calculated field :
One third of Unit Count = IF((accounts[Combined Units] < (SUM(accounts[Unit Count])/3)), "Less than one thrid", "Greater than one third")
When I tried to count Accounts it only shows "Less than one third" but not showing "greater than one third". (screen-shot attached)
HI @poojik,
When you do a measure it's calculated based on context, so the formula that you have is calculating the division by the current row it will give you 1, you need to something like this:
One third of Unit Count = IF ( accounts[Combined Units] < CALCULATE ( ( SUM ( accounts[Unit Count] ) / 3 ), ALL ( Accounts[Unit Count] ) ), "Less than one thrid", "Greater than one third" )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |