Using filters to change value/column in view

[michellmadeira]

Guys, 

I want to work with a report that contains 2 values: quantity sold and income.

But I wish I could show them on the same line graph, and individually (by filter control) also in the same line graph, like this:

The first one show the hybrid version - qty. and income.

The second ones are possible visions, that could be controled by a hypotetical filter.

Is this possible? 

Do you have any better suggestion to mix indicators on the same report?

I'd like to combine related indicators on the same reports because I work with blocks of information that also have some relation (below).

Combining indicators on the same graph, and filtering by demand, would better distribute information on the report, helping me visualize evolution of the means along the period:

Thanks,

Michell Madeira

[michellmadeira]

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?

[v-caliao-msft]

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

[michellmadeira]

Charlie, 

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

[michellmadeira]

I will start testing it soon, and will return to you, my friend.

Thanks!