Forum Discussion
Using filters to change value/column in view
I have tested some possibilities during the last hours, and found a possibily solution, but I am still believe there is a better way to do that, and would aprecciate some external help.
We may take the following table as the parameter:
| Date | Detail | Sales | Qty |
| 01/jan | Games | 1800 | 3 |
| 02/jan | Games | 1500 | 6 |
| 03/jan | Games | 3000 | 8 |
| 04/jan | Games | 2500 | 7 |
| 05/jan | Games | 2500 | 5 |
| 01/jan | Computers | 3000 | 15 |
| 02/jan | Computers | 1800 | 9 |
| 03/jan | Computers | 4400 | 22 |
| 04/jan | Computers | 3600 | 18 |
| 05/jan | Computers | 4000 | 20 |
There we have sales and quantity, by "date" and "detail" (games/computers).
As a graph, I wish they could work like this:
So, the only solution I've found was to reconstruct the table like this:
| Date | Filtro | KPI | Values |
| 01/jan | Games | Sales | 1800 |
| 02/jan | Games | Sales | 1500 |
| 03/jan | Games | Sales | 3000 |
| 04/jan | Games | Sales | 2500 |
| 05/jan | Games | Sales | 2500 |
| 01/jan | Computers | Sales | 3000 |
| 02/jan | Computers | Sales | 1800 |
| 03/jan | Computers | Sales | 4400 |
| 04/jan | Computers | Sales | 3600 |
| 05/jan | Computers | Sales | 4000 |
| 01/jan | Games | Qty | 3 |
| 02/jan | Games | Qty | 6 |
| 03/jan | Games | Qty | 8 |
| 04/jan | Games | Qty | 7 |
| 05/jan | Games | Qty | 5 |
| 01/jan | Computers | Qty | 15 |
| 02/jan | Computers | Qty | 9 |
| 03/jan | Computers | Qty | 22 |
| 04/jan | Computers | Qty | 18 |
| 05/jan | Computers | Qty | 20 |
The first problem is formatting (I would work with at least 3 ranges of informations - %, tens and thousands).
The second problem is to streamline this summarization, because I work with lots of informations.
Any idea on working with that?
Hi Michell,
I have tested you solutoin which wokrs fine. And actually you do not need to reconstruct the table, you can use a query like below.
select a.Date as [Date],a.Detail as [Detail],'Sales' as [KPI],a.Sales as [Value]
from
[dbo].[Test1] as a
union all
select a.Date as [Date],a.Detail as [Detail],'Qty' as [KPI],a.Qty as [Value]
from
[dbo].[Test1] as a
Or you can create view use that query, and use the view as the data source.
Regards,
Charlie Liao
I will start testing it soon, and will return to you, my friend.
Thanks!
I've did my best to transcript it to M (Power Query) and work it from the Advanced Editor on Power BI, but I couldn't.
I'm not so experienced in this engine, and in my world, it would be best to be done in that step.
Could you please help me just with the syntax?
I would invest my time to construct the function.
Thanks,
Michell