Avoid cummulative values on a line graph

[Anonymous]

Dear community,

I'm trying to figure out a line graph with of two different dimensions, for the same Category and period. I need two lines, instead of one, which is summing both.

I thought it might be from the sum tick within the Visuals section, which I can't disable it.

Any tip to solve this issue?  

[MFelix · Accepted solution]

Hi @Anonymous ,

You need to place the column Label in the Legenda part of the line chart, that will give you the total for each of the labels ES and DE.

[Anonymous]

Hi @MFelix ! Thanks for your help. In fact I tried that option today in the morning it and it worked. That's good that you have suggested the same approach, so it will be a solid solution. 

I would take this chance to ask you, if do you think it's possible also to add lines, along with the different "Labels" (Product), if we select more than one "Prazo" (Segment)?

[MFelix]

Hi @Anonymous ,

Do you want to have more than one type of information? You want to have lines by lables and by and Prazo at the same time?

What is the exact result you need.

[Anonymous]

Hi again @MFelix ! Thanks for your interest.

Yes, indeed I need two dimension for daily prices [Cotacao]: Label (product) and Prazo (segment).

For instance, if I filter the information by [Label]: ES; and [Prazo]: Spot and Q4-2021, I would have 2 lines: ES/Spot and ES/Q4-2021.

Another situation could be, if I filter by [Label]: ES , FR; and [Prazo]: Spot and Q4-2021, I would have 4 lines: ES/Spot and ES/Q4-2021 + FR/Spot and FR/Q4-2021.

Is that possible or I will be restricted to one dimension?

[MFelix]

Hi @Anonymous ,

Believe this can be achieve using a disconnected table with the options and a switch function.

This solution is based on a table of sales and using two different columns from different tables but you need to create a table that merges the two values from the columns you need in my case I did it by dax but you can also do it on the query editor.

DinamicAxis - RegionChannel = 
   FILTER( UNION(
        ADDCOLUMNS(SELECTCOLUMNS(SUMMARIZE(Geography, Geography[ContinentName]),"Region/Channel",Geography[ContinentName]),"Attribute", "Continent"),
        ADDCOLUMNS(SELECTCOLUMNS('Channel',"Region/Channel",Channel[ChannelName]),"Attribute", "Channel")
        ),
[Region/Channel] <> BLANK())

Table is the following:

So basically I picked up the information for other two columns with much more information:

Continent:

Channel

Now you just need to create a measure with the following code:

Dinamic Axis Sales = 
SWITCH (
    SELECTEDVALUE ( 'DinamicAxis - RegionChannel'[Attribute] ),
    "Continent",
        SUMX (
            FILTER (
                SUMMARIZE (
                    sales,
                    Geography[ContinentName],
                    "TotalSales", SUM ( Sales[SalesAmount] )
                ),
                Geography[ContinentName]
                    = SELECTEDVALUE ( 'DinamicAxis - RegionChannel'[Region/Channel] )
            ),
            [TotalSales]
        ),
    "Channel",
        SUMX (
            FILTER (
                SUMMARIZE (
                    sales,
                    Channel[ChannelName],
                    "TotalSales", SUM ( Sales[SalesAmount] )
                ),
                Channel[ChannelName]
                    = SELECTEDVALUE ( 'DinamicAxis - RegionChannel'[Region/Channel] )
            ),
            [TotalSales]
        )
)

Now you can use the values on your table in the legend of the lines and on a slicer:

Please check attach PBIX file.

[Anonymous]

Hi @MFelix ! Thanks for your explanation.

I think is great idea for this workaround, the example that you gave! I tried to replicate it on my case, but I realized that insted of giving the aggregated Sales for a single atributte, like "online" or "north america", I would need a line like the sales of "North America" in "Online", "North America" in "Store", a line of "Europe" in "Online", "Europe" in "Store".

Is that possible to do it?

Thanks a lot.

Regards

[MFelix]

Hi @Anonymous ,

In this case you can do one of two things:

1. Add a column to your original table with the following syntax:

Country / Continent = Geography[ContinentName] & "|" & Geography[RegionCountryName] 

Use this column on the legend of your line chart and the other two columns on the slicers.

2 Crate a table has the two columns in the same table and then change the measure to pick up those values the table will be something similar to:

Continent/Region = 
ADDCOLUMNS (
    SUMMARIZE ( Geography, Geography[RegionCountryName], Geography[ContinentName] ),
    "ID",Geography[ContinentName] & "|" & Geography[RegionCountryName] 
)

This table will look like this (table of unique conditions between country and continent):

Now redo your measure to:

Dinamic Axis Sales_ = 
        SUMX (
            FILTER (
                SUMMARIZE (
                    sales,
                    Geography[RegionCountryName], Geography[ContinentName],
                    "TotalSales", SUM ( Sales[SalesAmount] )
                ),
                Geography[RegionCountryName]
                    = SELECTEDVALUE ( 'Continent/Region'[RegionCountryName] ) &&
                Geography[ContinentName]
                    = SELECTEDVALUE ( 'Continent/Region'[ContinentName] ) 
            ),
            [TotalSales]
        )

Use the ID column on your legend and the other two columns on the slicers:

[Anonymous]

Hi Miguel!

Sorry for the late feedback. I'm trying work on other developments, so I will stand-by this specific one. Nevertheless thanks for your patience and help. 

I will come back to solve this one soon.

Best regards