cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Two Bar chart separate with different values

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

2 ACCEPTED SOLUTIONS
Frequent Visitor

Ç

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

Frequent Visitor

CategoriaPorcentajeCOUNT (subcate) =
DIVIDE(
COUNTX('Table', 1),
CALCULATE(COUNTX('table', 1), ALLSELECTED('table'[category]))
)
8 REPLIES 8
Frequent Visitor

Ç

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

Resolver II

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.

Frequent Visitor

CategoriaPorcentajeCOUNT (subcate) =
DIVIDE(
COUNTX('Table', 1),
CALCULATE(COUNTX('table', 1), ALLSELECTED('table'[category]))
)
Resolver II

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

Frequent Visitor

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

Frequent Visitor

Hi @TobyNye
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

Resolver II

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.

Resolver II

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors