How to do running total based on two columns in power BI

[sepehr485]

Hello all. I am new to PBI and trying to use Dax for visualizing a part of my data. I have a dataset consist of Region, Status, Date ,Count. I am trying to show the cumulative Count based on the status and month in a clustered chart with lines in PBI. I want to show the cumulative count of act and plan as a separate line. in the first action I need to be able to make a measure for cumulative based on months and status. The Dax formula I wrote is not showing the cumulative count. Cna someone help me to fix it? Thanks.  

Cumulative Target =
CALCULATE(SUM('Sheet1'[Count]), ALLSELECTED('Sheet1'[Type]),'Sheet1'[Type]="Pln")

[daXtreme]

// For such things you have to have a good
// model. That means: facts and dimensions.
// Otherwise, you'll be struggling and it'll
// never be correct. So, first you have to create
// your facts and dimensions. Once you have
// them, you could try this code:

// Measure 1
[Total Count] = SUM( FactTable[Count] )

// Measure 2
[Cumulative Count (absolute)] =
var CurrentDate = MAX( Dates[Date] )
var Result =
    CALCULATE(
        [Total Count],
        Dates[Date] <= CurrentDate,
        ALL( Dates )
    )
RETURN
    Result
    
// Measure 3
[Cumulative Count (relative)] =
var CurrentDate = MAX( Dates[Date] )
var Result =
    CALCULATE(
        [Total Count],
        Dates[Date] <= CurrentDate,
        ALLSELECTED( Dates )
    )
RETURN
    Result

Here's documentation on what it means "good dimensional model" in PBI: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

[sepehr485]

Thank you for your Reply . Well as of now I dont have the Star Schema, and it probably takes a lot of time to build that. I have seen other posts about runing total and there wasnt any talk about such topic. the only difference was my cumulative is based on two criteria. so I wasnt sure how to edit it to work. lets wait and see if others contribute to it. Thanks

[daXtreme]

What you're doing is very typical of people who start their PBI journey. I'd warn you against it but it's your choice. If your model is correct (and yes, you HAVE TO take time to build it now), your DAX will be simple and fast. If you start working with a bad model.... you'll feel the heat in no time (guaranteed). I tell you this as an experienced DAX programmer - I've got 6+ years of hardcore DAX programming experience with the biggest cubes that exist in the world. Take it or not. Your choice. But you've been warned, so to speak.

[sepehr485]

I appreciate it. I understand. but for the interest of time, I need to implement it in other ways and show it for approval. Then if there was decision to have such viz, I can continue. Otherwise I dont want to waste my time on something that is not going to to continue. So for now here is what I came up with. But the problem is the cumulative counts come all in one line and all numbers are sum together which is wrong. When I vizualize it. I need to have two separate running total line, one for pln and one for act. I put it in pbix file to submit but I dont see any option to attach it. 

[tamerj1]

Hi @sepehr485 

actually this is not the problem. You can just add the [Type] column to your chart and the curve will be split into two. But the problem is that you will get wrong values. 
If you take the first row in the sample data screenshot you've shared you will see that the date is Jan. 22 and value is 5 for North - Act. 
8 rows down (row # 9) you will find a pretty much duplicate row. I will assume that this row is just another record which happens to have the same value (5). 
The rows are not sorted by date but I can tell that the first date for Type Act is Jan. 6 which has a value of (6) (row # 5). 
Now its is clear how the engine calculated the value 16 (that is 5 + 5 + 6) which is the aggregated value of Jan. 22 and before. 
This value (16) is repeated in row # 1 and row # 9. When you pull the column into the chart visual, the engine creates an implicit measure of aggregation based on your choice which I bet is SUM. Therefore, if you hover over date Jan. 22 the curve will show a value of 36 as a running total which is wrong. 
This is just an example of what @daXtreme was trying to explain. Allways follow standards and rules otherwise, it is very possible that you make mistakes. One of the rules is never add columns of values to visuals. Allways create your own measures. 
Sometimes you need to create columns but this time creating a column does not make a lot of since therefore it is better to create the measure directly from source data. 

Rolling Total Measure =
CALCULATE ( SUM ( Sheet1[Count] ), Sheet1[Date] <= MAX ( Sheet1[Date] ) )

In the visual you can slice by Date and Type

[sepehr485]

Thank you for your Response. Can we do the star schema concept in out of the PBI environment and bring thr tables in PBI? or everything has to be done in PBI environment? 

Lastly, for the interst of time, is there any solution for this without entering into star schema concept? again, just for the interest of time. other I know the best way would be Star Schema. Thanks

[tamerj1]

@sepehr485 
Yes you can import the data model from SQL for example. I have no idea how many tables you have or you need. However, The solution I provided might work without.

[sepehr485]

Well, I have 2 tabels which is shown above as one consolidated dataset. one for Act and one for Plan. so the result I have is shown perfectly fine in the table. however when I put it in the Viz it doubles up as you said, and that what I need to fix. basically I need to maje the formula to count th emax number for every month and do the running total .

Thanks 

[tamerj1]

Hi @sepehr485 

please try

Rolling Total Measure =
CALCULATE (
    SUMX ( VALUES ( Sheet1[Date] ), CALCULATE ( MAX ( Sheet1[Count] ) ) ),
    Sheet1[Date] <= MAX ( Sheet1[Date] )
)

[sepehr485]

Thank you TamerJ. Below is the result. It still doesnt do cumulative sum.  It basically should take the total count of the counts for the first month, then add to the total count  of the next month and so on (cumulative ). but as you see it just drops when there is no count for any month or its lower count compare to th eprevious month. it always should be straight up. Thanks

[tamerj1]

@sepehr485 

This is why you need a date table. Anyway you can also try

Rolling Total Measure =
CALCULATE (
    SUMX ( VALUES ( Sheet1[Date] ), CALCULATE ( MAX ( Sheet1[Count] ) ) ),
    Sheet1[Date] <= MAX ( Sheet1[Date] ),
    REMOVEFILTERS ( )
)

[sepehr485]

Thank you Tamer. Numbers still doesnt add up correctly. the snapshot below shows the count in Viz and in the table. 

  

for the second month is also off by 1. but for the 3r dmonth is off by alot . 

 at this time,I am not sure if there is any solution for it other than star schema. But I really appreciate your help. 

[tamerj1]

@sepehr485 

The code is supposed to be for a measure not a calculated coulmn

[sepehr485]

but shouldnt the numbers match? I mean how do you check the calculation is correct?

[tamerj1]

@sepehr485 

To be honest I'm not sure if the measure will provide the correct results but it worths nothing to try. Create a new measure and place it in the chart. Copy the chart and conver it into a table visual and check the values. 

[daXtreme]

You can create the schema in the source (e.g., SQL Server) or in Power Query. If you have such a schema in the source, it's easy to just import into PBI, or even create a DQ connection.