Forum Discussion

migueldfr's avatar
migueldfr
Helper IV
2 years ago
Solved

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

 

Thank you all in advance 

Show and Tell
  • migueldfr's avatar
    migueldfr
    2 years ago

    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's avatar
    migueldfr
    2 years ago

    I found my issue, the right code was this: 

     

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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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. 

  • migueldfr's avatar
    migueldfr
    Helper IV
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.

  • migueldfr's avatar
    migueldfr
    Helper IV
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        migueldfr
        Helper IV
        2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        migueldfr
        Helper IV
        2 years ago

        I found my issue, the right code was this: 

         

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