Forum Discussion
Calculate % based off total, rather than selected
I'm getting there.
For some reason, it is multiplying, and not dividing.
%Cancelled = COUNTX(FILTER('Dec 2016 Closed Changes',
'Dec 2016 Closed Changes'[Closure code]= "Cancelled"),
'Dec 2016 Closed Changes'[Closure code]) / [TotalCloseCode]
It is coming out with a value that is the product of the total Cancelled and the TotalCloseCode.
edit: Even if I use just part of the formula
TotalCancelled = COUNTX(FILTER('Dec 2016 Closed Changes',
'Dec 2016 Closed Changes'[Closure code]= "Cancelled"),
'Dec 2016 Closed Changes'[Closure code])
The value is coming out with the Total # of cancelled x the total number of rows. I'm not sure what I'm doing wrong. I'm determining this by checking that row to add the card visual
Ok, I figured it out - it was a new measure I needed. The new column would just populate the value all the way down all the rows, which is why it was as if multiplying. When I created the new measure for the %Cancelled, I was able to obtain the value needed.
Thank you for all the help vitinho
I was just thinking,
So - i renamed the dataset so that it is just "Closed Changes" as I will be appending new months to the table.
%Cancelled = COUNTX(FILTER('Closed Changes',
'Closed Changes'[Closure code]= "Cancelled"),
'Closed Changes'[Closure code]) / [TotalCloseCode] * 100But - if I append a new month of data on, that will mess up the results won't it? Using this formula above, I won't be able to provide the monthly values, will I? I'm assuming since the TotalCloseCode (which is TotalCloseCode = COUNTROWS('Closed Changes') when I add the new month on, this %cancelled will be based on two months of data. I won't be able to filter out each month of data.
Am I correct? If yes - I need to rethink how this is done, because I need to be able to obtain these values monthly
I've tried here and you're a good to go with this model.
If your columns are likeTable1
ColumnA Result Month
Power Bi will automatically divide you data by month once the month is set as axis of your two line graphic
Wouldn't this new measure be incorrect though?
TotalCloseCode = COUNTROWS('Closed Changes')
This would count the rows of all data rows that keep getting appended. But I would be calculating the percentage individually for each month. How can I get it that it will only count the rows individually for each month, without having to add a new measure each month ?
Can anyone confirm it is possible to calculate percentage of just those selected month over month, where new data will continue to be appended? If I use the Countrows option, it will count all the rows for all the months. Unless there is another way to calculate the percentage so that it keeps the true percentage and doesn't recalculate % values after doing selections/filtering.
I don't want to have to add a new measure every month, else in a couple of years, there will be quite a few new measures.
