Forum Discussion
Calculate percentage difference between two column-individual row and difference of column for month
I have a requirement to show the percentage difference between two columns dynamically based on user selection in filter.
I am providing 3 individual filters to user as Slicer - Region, Country, Month.
The query editor has data in below format:
Column : Country, Region, Month, Year, Sales 1, Sales 2, Sales % (Sales2/Sales1)
By using the above format, the % is shown as SUM for all region but it has to be a calculated difference of Sales 2/Sales1 for each month. When country filter is applied the percentage is displayed correctly.
What i need is to show the percentage difference between both sales dynamically but when no filter is applied it has to show difference data calculated on month basis.
Current Data Source reference:
By using the above format, the % is shown as SUM for all region but it has to be a calculated difference of Sales 2/Sales1 for each month. Required Output Eg. January = Sales2/Sales1 = January Sales %
6 Replies
tamilkumaran - I could be missing something here but it seems like you should just have to create a measure like:
Measure = DIVIDE(SUM('Table'[Sales 2]),SUM('Table'[Sales 1]),0)That should work for any filter context you wish to apply, month, month and country, etc.
Greg_Deckler amitchandak FrankAT
Let me try to explain in a better way, the outcome i am expecting is.
- If no filters or Region filter is applied by the user the chart should show the percentage by calculating the difference between two columns by SUM of January Sales 2/January Sales 1 - same way for other months Feb, March...etc
Region Filter
- If Country Filter is selected by user then the chart should show the percentage by calculating the difference between two columns by each row of the selected Country for that month.
I am very sorry if i am still not able to explain it to you clearly but this is my requirement to show the data.
Thank you for helping me.
tamilkumaran - Still not sure what you are going for here, but for your requirement you can switch how something is calculated by checking ISFILTERED. So:
Measure = VAR Calc1 = <some calculation by month> VAR Calc2 = <some calculation by country and month or i'm not really sure> RETURN IF(ISFILTERED('Table'[Country]),Calc2,Calc1)
tamilkumaran , not very clear, you can use time intelligence and date table
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) previous month value = CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date])) or diff between two sales Measure = divide(SUM('Sales '[Sales 2]),SUM('Sales '[Sales 1]))https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Hi tamilkumaran
is this what you are searching for?
Sum of Sales 1 = SUM('Table'[Sales 1]) Sum of Sales 2 = SUM('Table'[Sales 2]) Percentage = DIVIDE([Sum of Sales 2],[Sum of Sales 1])Regards FrankAT
HI tamilkumaran,
You can take a look at following blog if you are try to use filter to make axis dynamic:
Dynamic Attributes In A Power BI Report
After you modify your table structure, you can use the formula that Greg Deckler shared to combine with a dynamic attribute filter to achieve a dynamic visual.
Regards,
Xiaxoin Sheng
