Sorry for the basic question, but I have a table that has the following fields:
- Transaction Date
I need to biuld a view to show the info grouped by Category, subcategory and revenue. And also be able to filter by transaction date.
The solution I found was to group the info from the original table, but on that case I'm removing the Transaction Date to have the info grouped by Category and subcategory, reason why I can't use the date field later for filtering the information.
How can I solve that? What Am I missing? How can I include the date back for using it as s filter on the grouped info?
maybe i'm missing some important fact, but why just create a slicer from the columns, whatever you select in the slicer filters / groups the rows in your table.
Maybe you also might consider to create a separate date table and use relationshops in you data model.
Hopefully this is what you are looking for and I'm not overlooking the obvious
Hi Tom, thanks for the reply. Maybe I'm missing something, but when I create a slicer on the raw data, and apply a filter (let's say February), the data that will be displayed are all the rows and not filtered. That is why I built the grouped info as shown below, but when I did that I lost the date field
Any help will be great
what I described above is basically the magic of Power BI, besides some ohter things 🙂
Can you provide a screenshot from the "Relationships" pane something similar like this
or better provide sample data?
Sure!. This is the sample of the data. I need to chow the info summarize by Category and subcategory whoing the revenue and being able to filter it by the month the case was created. Let me know if this is clear.
|Case Number||Proactive or Reactive||Status Reason||Category||Sub Category||Created On||Revenue|
|Case 1||Reactive||Closed (Resolved)||Category1||Subcategory1||29/03/2018 18:15||$ 450.000,00|
|Case 2||Reactive||Closed (Resolved)||Category2||Subcategory2||29/03/2018 14:40||$ 550.000,00|
|Case 3||Reactive||Closed (Resolved)||Category3||Subcategory4||27/03/2018 16:10||$ 312.000,00|
|Case 4||Reactive||Closed (Resolved)||Category3||Subcategory4||26/03/2018 16:09||$ 209.520,00|
|Case 5||Reactive||Closed (Resolved)||Category4||Subcategory3||23/03/2018 16:35||$ 468.000,00|
|Case 6||Reactive||Closed (Resolved)||Category2||Subcategory2||23/03/2018 9:48||$ 75.000,00|
thanks for providing the sample data, I assume that your data model is just one table w/o any separate Calendar table.
Here you will find a little pbix file, as the following screenshot shows the data gets filtered by the Created On column, due to the fact your sample data did not contain date values for different months I filtered the data using just a single day. For the filtering I used the "Timeline" visual from the marketplace:
Here is a screenshot from the same report where I adjusted one date value from your sample data to be in a different month, now I have 2 months to filter and it still works:
Nevertheless, can you please check if your revenue column is a numerical datatype - in no way it has to be "Text".
Mark the column, and check the datatype:
I had to adjust the values in the revenue column, because it has been inserted into PowerBI as text, due to spaces between the currency symbol and the value. If everything is fine you can recognize that the column can be aggregated by the Sigma sign in front of the column name:
You have to change the datatype of the revenue column to a datatype that can be aggregated, but this would be another question and an easy task for Power Query if necessary.
There is one thing that catched my eye, the column Created On also has a time information. In most of the cases I create a new column in my table w/o the time part and then I use this column to create a relationship with a separate Calendar table.
Wondering if this will help, currently I have no idea why the Power BI magic will not work in your case, or I still do not understand your requirement.
We are excited to announce the Power BI Super Users!
Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.