Forum Discussion
Visual filter to separate two different columns with values
Hey guys
I have a dataset that looks something like this:
[Product] , [Customer], [Time - Month], [Sales Month], [Sales YTD].
What I want to do is to be able to filter out weather a visual should be showing monthly sales or YTD sales.
Currently, I need two seperate visuals for this, but a slicer would be so much better.
Right now I seperate the two with measures such as SUM([Sales Month]) and SUM([Sales YTD]).
But if I could have measure like: SUM([Sales Month]) + SUM([Sales YTD]) on my visual, then next to my visual would be a single select slicer that could determine weather I display Month values or YTD values.
What am I missing here?
P.S - I always get Month + YTD data from the system, so I am not looking to change the underlying data really.
I only get monthly data from previous month, so YTD DAX time measures are not really working well for me either.
Any tips would be appreciated.
Rasmus
Hi,
According to your sample file, i think it is the same with my original post.
Please try to create a slicer table by Enter Data:
Then try this measure:
Sum of Periodic/YTD = SWITCH ( TRUE, SELECTEDVALUE ( 'New Slicer Table'[New Slicer Value] ) = "Sum of Periodic", SUM ( 'Rasmus - Table'[Periodic] ), SELECTEDVALUE ( 'New Slicer Table'[New Slicer Value] ) = "Sum of YTD", SUM ( 'Rasmus - Table'[YTD] ) )Choose this measure as a Card visual, [New Slicer Value] as a slicer.
When you select one in slicer, the result shows:
Here is the changed pbix file:
if i have solved your issue, please mark it as a solution for others to see.
Thanks!
Best Regards,
Giotto Zhi
10 Replies
We can create a column to subtract this month YTD from the previous month YTD. I suggest formula based the month format you have .
Assume you have formate like jan-2020
The create a date = "1-"& [Month Year], mark this has date
create new previous month column
Last date = Date(Year([Date]),Month([Date])-1, 1) //Assuming month level data
new column
Last month ytd = sumx(filter(table, table[product] = earlier(table[product])
&& table[customer] = earlier(table[customer])
&& table[date] = earlier(table[last date])),table[Sales YTD])Not clear, Do you want to exchange what to visualize
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
Can you explain with example
Hi,
According to your description, i create a sample to test:
Create a slicer table by Enter Data first:
Then try this measure:
Measure = SWITCH ( TRUE, SELECTEDVALUE ( 'Slicer Table'[Slicer Value] ) = "_Monthly Sales", CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', 'Table'[Year] IN FILTERS ( 'Table'[Year] ) && 'Table'[Month] IN FILTERS ( 'Table'[Month] ) ) ), SELECTEDVALUE ( 'Slicer Table'[Slicer Value] ) = "_YTD Sales", CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( ALL ( 'Table' ), 'Table'[Year] <= MAX ( 'Table'[Year] ) ) ) )Choose [Year],[Month] and this measure as a matrix visual, [Slicer Value] as a slicer.
When you select one value in slicer, the result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
Hi Giotto and thanks for showing interest in helping me out!
I guess the reason I can't use your solution is because you only have one data field called sales. I have two data fields, Periodic and YTD. I am happy to keep it that way, but want the slicer to reflect one of the two columns.
I made changes to the sample pbix. to include how my data is set up.
Maybe you can use similar logic to my sample table instead?Hi,
According to your sample file, i think it is the same with my original post.
Please try to create a slicer table by Enter Data:
Then try this measure:
Sum of Periodic/YTD = SWITCH ( TRUE, SELECTEDVALUE ( 'New Slicer Table'[New Slicer Value] ) = "Sum of Periodic", SUM ( 'Rasmus - Table'[Periodic] ), SELECTEDVALUE ( 'New Slicer Table'[New Slicer Value] ) = "Sum of YTD", SUM ( 'Rasmus - Table'[YTD] ) )Choose this measure as a Card visual, [New Slicer Value] as a slicer.
When you select one in slicer, the result shows:
Here is the changed pbix file:
if i have solved your issue, please mark it as a solution for others to see.
Thanks!
Best Regards,
Giotto Zhi
