Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
migueldfr
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) 

migueldfr_0-1700124193815.png

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 : 

migueldfr_1-1700124327339.png

All the percetages sum 100% but only along 2023

 

Thank you all in advance 

2 ACCEPTED SOLUTIONS
migueldfr
Frequent Visitor

Here, i upload and example: 

migueldfr_1-1700147485593.pngÇ

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 

 

View solution in original post

I found my issue, the right code was this: 

 

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

View solution in original post

8 REPLIES 8
migueldfr
Frequent Visitor

Here, i upload and example: 

migueldfr_1-1700147485593.pngÇ

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 

 

Think I found the issue!

TobyNye_0-1700148569210.png

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.

I found my issue, the right code was this: 

 

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

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

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

migueldfr
Frequent Visitor

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

migueldfr_0-1700146398325.png

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


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.

TobyNye
Resolver II
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. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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