Sum by Group and help with bar graph

[isaac152]

Hi, i know this subject is very common but the other answer doesnt help me to much, so i need to ask.

I have a table like this

Shop1		07/2019		A	15
Shop1		07/2019		B	20
Shop1		08/2019		A	25
Shop1		08/2019		B	30
shop1		12/2019		A	35
shop1		12/2019		B	40
shop2		7/2019		A	45
shop2		7/2019		B	50

I need this:

A) Sum all the numbers(last column) given a specific date. For example: Shop 1 in 07/2019 =35

B) Calculate the difference between one date and another (next months), but only for the same shop. For example: Shop 1 in 08/2019 = 55 So my new value will be 55-35= 20

C) Put that data into a bar graph, no the sum or average, just the data by shop and date.

For the first problem, i tried something with IF but i dont know how to verify the shop and the date.

For the last problem i have an issue with the default value of bar graph. I jus want to show a specific value for a graph, yes, i know, i can use a fitler, but the problem is, if i apply a filter by a date, for example, i cannot interact with a slicer for date.

But if i delete the date filter, the graph will show a sum/average of the year and that doest have any sense for the data.

I dont know if i this is too much for just one post, but i am complete begginer in power bi.

BTW if i did some mistake, sorry for that and, of course, for my english.

[v-xicai · Accepted solution]

Hello @isaac152 ,

For your lawsuit:

1. A) Sum all numbers (last column) given a specific date. For example: Store 1 on 07/2019 35
2. B) Calculate the difference between one date and another (next months), but only for the same store. For example: Store 1 on 08/2019 to 55 So my new value will be 55-35o 20
3. C) Place that data in a bar chart, without the sum or average, only the data by store and date.

For demand option A and B , you can create the calculated column [YearMonth] first, and then create a measure as DAX below.

Column:

YearMonth= VALUE(FORMAT(Table1[Date]), "yyyymm"))



Measure:

Sum per Shop = CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[Shop], Table1[YearMonth]))



Differ Sum per Shop =

Var _LastYearMonth= CALCULATE(MAX(Table1[YearMonth]), FILTER( ALLEXCEPT(Table1,Table1[Shop], Table1[YearMonth]), Table1[YearMonth]<MAX(Table1[YearMonth])))

Var _LastAmount= CALCULATE(SUM(Table1[Amount]), FILTER( ALLEXCEPT(Table1,Table1[Shop], Table1[YearMonth]), Table1[YearMonth]=_LastYearMonth))

Var _CurrentAmount= CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[Shop], Table1[YearMonth]))

Return

_CurrentAmount -_LastAmount

For demand option C, you can put [Date] in the Visual chart axis box, put [Buy] in the Legend box, put [Quantity] in the Visual chart values box. Usually, the [Quantity] will be displayed as a "Sum" aggregation based on the [Date] and [Store] group, and there is no "Do Not Summarize" option behind Values for the visual chart, which is by design.

Best regards

Amy

Community Support Team _ Amy

If this post helps,then consider Accepting it as the solution to help other members find it more quickly.

[isaac152]

Hi, thanks now i can sum by shops. That help me a lot in other graphs. But the difference didnt work to me. I tried to use your formula for difference but "lastyearmonth" return the actuan date not the last. I dont understand why.

What i did now is a pivot table with Shop names, Date and total amount by shop per month. And i am trying to make a diff with that.

The problem in the graph is i cant use the shop as legend, because i need to compare two metrics (blue and yellow), i know in the example looks the same but in all the data is doesnt. I guess the solution is make a default value for the graph. I am watching some tutorials with a slicer but i dont know if they work with data slicer.  Anyways thanks you so much for the help

[lbendlin]

Have you tried the Waterfall chart type?  It will give you the answers for free (ie without coding).

[isaac152]

Hi, thanks for answer me. I need (for multiple reasons) a bar graph. I tried to set a default value with date and slicer, but i dont understand how that could be work in a range of values.

For example, show the value of the max month available and change the graph if you move the data range. Is that possible? If the answer is yes, can you please tell me how to start ?

[lbendlin]

Let's start with A and B

[isaac152]

That just give me a big number at the end of the table. I guess that just search for the max date and then compare with the other max date.  If you dont mind, and i know i am asking to much, but can you tell me  how to sum by a group, in this case just by Shop in a date. Example

Shop1 07/2019 = 44

Shop 1 08/2019 = 33

And go on.

I think is something like = Calculate(Sum(Table[amount], __________)

I dont know what to put here, in a programming language that could be a interactive comparation between rows but here i dont undertstand.