Two Bar chart separate with different values

[migueldfr]

Hi folks,

Two Bar chart or same similar 

I would like to Sum the amount of sales by store, differentiating by year, but those year do not have to be correlated.

That means, the percentage is between the unique year, not all graph. (Atached image below) 

numbers means the stores, and percetages means number of items has been sold againts the total in that year...
But I want differenting percentages by those two year, because they are correlating between them.

Look at with 2023 : 

All the percetages sum 100% but only along 2023

Thank you all in advance 

[migueldfr · Accepted solution]

Here, i upload and example: 

Ç

I create a table with values and the stores but as you see, the TG% de CANT. is not calullating the values by the total of that colum, it is calcullating by the total of all table...
I don't want that, I would like to visualize the data for 2022 showing the percertage of that year, and same 2023, but separatly.
Thank you 

 

[migueldfr · Accepted solution]

Here, i upload and example: 

Ç

I create a table with values and the stores but as you see, the TG% de CANT. is not calullating the values by the total of that colum, it is calcullating by the total of all table...
I don't want that, I would like to visualize the data for 2022 showing the percertage of that year, and same 2023, but separatly.
Thank you 

 

[Anonymous]

Think I found the issue!

See above, I believe this is working as you want, though not on the same visual, just easier for me to look at, I replaced the _sales argument in _sales_this_year with SUM('YourTable'[Sales]) and it works. I imagine this is something to do with variables having a fixed value after declaring them and then not obeying filter context, not sure why but it seems to be what's happening in this case, could be something to do with calculate also.

[migueldfr · Accepted solution]

I found my issue, the right code was this: 

CategoriaPorcentajeCOUNT (subcate) =

DIVIDE(

    COUNTX('Table', 1),

    CALCULATE(COUNTX('table', 1), ALLSELECTED('table'[category]))

)

[Anonymous]

Sorry, I meant the tables that create your data model and their relationships, I think that will be where the issue lies. I assumed how they were setup but if you have created the relationships differently to my assumption then the measures would need tweaking based on that, I'll try building an example .pbix and test the results

[migueldfr]

I can send yopu the data, but I don't know how to send to you.
Thanks

[migueldfr]

Hi @Anonymous 
Thank you for the answer.
But i tried to create that measure and get into my chart, but It doesn't work.

the code i wrote it down is same, but changing my names, obvisusly.
What could it be ? 
Thank you 

[Anonymous]

Would be good if you can send me some sample data so I can play around with it myself if possible. Looking at it returning 100% everywhere though it would infer that the two variables in the measure are returning the same thing. Are the values for the stores in a separate table? You'd want the table inside FILTER(ALL()) to be the table with your stores and sales numbers, try the below unless this is already what you've done:

VAR _sales = SUM('YourSALESTable'[Sales])

VAR _sales_this_year = CALCULATE(_sales, FILTER(ALL(YourSALESTable),

                                    YEAR(SELECTEDVALUE('YourDATETable'[Date])) = YEAR('YourSALESTable'[Date]))

RETURN

DIVIDE(_sales, _sales_this_year, 0)

The argument with YourDATETable should be whichever date field you have in the graph visual itself. If none of that then I'd guess that the legend is potentially just a year column and not a date column? If that is the case you can try the following:

VAR _sales = SUM('YourSALESTable'[Sales])

VAR _sales_this_year = CALCULATE(_sales, FILTER(ALL(YourSALESTable),

                                    SELECTEDVALUE('YourDATETable'[Year]) = YEAR('YourSALESTable'[Date]))

RETURN

DIVIDE(_sales, _sales_this_year, 0)

If none of that works, I'd need to see the data myself, hope this helps, let me know how you get on.

[Anonymous]

The following should work:

Test = 

VAR _sales = SUM('YourTable'[Sales])

VAR _sales_this_year = CALCULATE(_sales, FILTER(ALL(YourTable),

                                                                    YEAR(SELECTEDVALUE('YourTable'[Date])) = YEAR('YourTable'[Date]))

RETURN

DIVIDE(_sales, _sales_this_year, 0)

This effectively returns the total sales respective of the data and store, divided by the total sales only respecting the year context which should be what you're after. Hope this helps, let me know if you have any issues or further questions.