Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a column A which needs to be divided by column B and the resultant value needs to be a percentage displayed in a graph
For example : the formula should be like this % P = (A/B) * 100
how do i create the dax formula for the resultant measure
the users will filter it based on the month and year and should be able to see %P based on the month and year in the graph
A simple way would be to create 3 measures. The first two would be the sum of Column A and B respectively. Something like:
SumColumnA = sum('TableName'[ColumnA])
Then you can create the measure:
PercentP = [SumColumnA] / [SumColumnB] * 100
Hi Ross
I want a row by row percentage. Your solution will give the percentage for the entire column
P = A/B * 100 should give me the percentage for each row of A and B
If you want a Row by Row version, create a custom column and use a formula:
PercentP = [ColumnA] / [ColumnB]
If this row by row version needs Column B to be the entire column, replace that [ColumnB] part of my formula to reference this measure:
SumColumnB = Calculate( Sum('Table1'[ColumnB]), ALL('Table1') )
This measure could be updated to use ALLEXCEPT or ALLSELECTED depending on what splicers you wish to take into account.
Hi Ross,
Thanks a lot for your reply
I created a calculated column which would give me the percentage
when i try to include it in a line chart the percentage gets summarized and the legend shows the "SUM of meaure" and i dont see an option to edit that to an appropriate name
I am attaching a screenshot of my report which will help you get an inisght of what i am trying to achieve
Hi @rnadathur,
The Values scetion for the line chart need to be aggregated. In your scenario, as you create a calculated clumn and place it in Values property, it will be set with one kind of aggregate function, eg: SUM. So the legned name is changed with that aggregate function.
I would suggest you to create a measure instead of the measure with below DAX:
Percentage= DIVIDE(SUM([ColumnA]),SUM([ColumnB]))
then set Format as Percentage for this measure.
Best Regards,
Qiuyun Yu
Click on the visual and look under the Visualisation menu on the right of your screen. There is a paintbrush section where you will have the Toggle Option to display the Title or not. Expanding this there is an area called "Title Text" you can update.
Is that what you were hoping to change?
Hi Ross
I am looking to change the legends "SUM OF %Timely" to just "Timely" and "SUM OF %Untimely" to just "Untimely"
I tend to use a lot of measures, so a straight forward solution could be to create a Measure that does the sum for you, then when it appears in your Legend it will have the nicer name?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |