Forum Discussion
Dynamic percentage based on attributes applied
Ok great, the formatting must have been the elusive element.
Doing some quick maths:
208764.42 / 37968039.17 = 0.005498425. As a percentage that should be 0.54%. So that means you are correct in that its 100 times too small, plus the number itself is wrong.
Lets create 2 more measures.Numerator = SUM('YourTable'[SumActualCost]) Denominator = SUM('YourTable'[Total Cost])Bring those into the matix next. I'm curious what that comes up with.
For the reason the reason it doesn't add to 100%, i believe My error is with Total Cost. Instead of SUM for the Denominator, we should use MAX instead. This is because you don't actually want to sum up that column.
No definately not, thats not necessary. Does your formula now look like?
Simple % = DIVIDE( [Numerator], [Denominator] )Remembering Numerator and Denominator are measures, so we can call them directly in our new Measure.
Where are you putting the measure to be displayed? What filter contexts are used? Getting zero in this manner makes me suspect there is another element in play here.
The measure is created within this table. Which was originally two tables, but merged together in the Query Editor and then I used the Group By function and specified these columns, and aggregated the Actual Cost. No filters have been applied. The visual I'm viewing the result in is a table, and I had to specify the Total Cost column to "Don't Summarize".
Just to clarify, The matrix visual in your screen shot has each item entirely from this single grouped table?
What does it look like if you bring the Measure onto your table, so you get a row by row result?
Is the table used for the matrix visual the same table referenced by the measure?
The table in my original post shows the layout of the table itself, but it's only a small subset of the data in the table and I've changed the details in the data.
I've tried bringing all fields into my table visual together with the measure, but the result is still 0 for every row.
Yes, the measure has been created within this table, references only fields from this table and all fields displayed in the visual are from the same table as the measure.
There is definately another element here that is not appearent to me.
If the data shown in the matrix all comes from fields within the same table, and the measure you we are using only references that same tables and its fields, there is no way for it to produce zero without those numbers also being zero.
Since it is showing zero, something isn't true about the above.
I've just changed the Format of the Measure to "Percentage" and now I'm seeing numbers but the total is 0.0087184% (shouldn't this be 100%?) and the row values aren't correct (see screenshot for example). I've tried multiplying the measure by 100 and this seems closer to what it should be, but as you can see below, a percentage for 2.3m is lower than 350k...?
Ok great, the formatting must have been the elusive element.
Doing some quick maths:
208764.42 / 37968039.17 = 0.005498425. As a percentage that should be 0.54%. So that means you are correct in that its 100 times too small, plus the number itself is wrong.
Lets create 2 more measures.Numerator = SUM('YourTable'[SumActualCost]) Denominator = SUM('YourTable'[Total Cost])Bring those into the matix next. I'm curious what that comes up with.
For the reason the reason it doesn't add to 100%, i believe My error is with Total Cost. Instead of SUM for the Denominator, we should use MAX instead. This is because you don't actually want to sum up that column.