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.
Hi All -
I've tried all suggestions on running totals using DAX. I haven't had any luck though.
Can you sum a measure?
Call Counts = COUNTROWS(CallTable)
I also tried creating a custom Calculated Column to see if that would work but both columns are still the same. It's not accumulating.
Running Total Test =
CALCULATE (
SUM ( CallTable[Call Count Col] ),
FILTER (
ALL ( dimDate[Date] ),
dimDate[Date] <= MAX ( dimDate[Date] )
)
)
Also, tried -
Running Total Test2 = IF(MIN(DimDate[Datekey])<=CALCULATE(MAX(CallTable[DateKey]),ALL(CallTable)),CALCULATE([Call Count],FILTER(All(DimDate[Datekey]),DimDate[Datekey]<=MAX((DimDate[Datekey])))),BLANK())
Ultimately, need to running total a Call count column which is a measure. I can't seem to SUM a measure. Any help is greatly appreciated! Thank you. Using a Matrix for testing purposes; the Row ='s Customer Name; Column = Month (1-12); Values = Call Count and Running total. If I can get the running totals properly I will just use that column.
I found my solution! Used COUNT instead of SUM.
I have a matrix by Month, Customer Ranking and counts, what can I do to fill the blank rows with 0. I've tried ISBLANK however that didn't work. Also, need to always show months 1-12.
Month 1 2 3 4 5 6 7 8 9 10 11 12
Customer Rank
1 1 1 1 2 2 2 1 3 4 5 1
2 1 1 1 2 2 2 1 3 4 5 1
3 1 1 1 2 2 2 1 3 4 5 1
4 1 1 1 2 2 2 1 3 4 5 1
Need 0's for the Month #3.
Thank you.
A common Measure that you’ll probably find useful in PowerPivot or SSAS Tabular Models is finding running totals. For example, you may want to see total sales of a product as it accumulates over time, or for inventory models the total on hand at a given time. You can find more tips and tricks at my blog, www.bipatterns.com.
Let’s start with a base measure in a very simple pivot table.
Total Sales :=
CALCULATE ( SUM ( FactSales[SalesAmount] ) )
Now lets take our first attempt at computing a running total. This is the most intuitive formula, but it has one common pitfall that isn’t necessarily easy to see right away.
Cumulative Total Sales :=
CALCULATE (
[Total Sales],
FILTER (
ALL ( DimDate[Datekey] ),
DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
)
)
Key parts of the Formula: The use of ALL(DimDate[DateKey]) results in the current context being ignored, so dates outside of the current pivot row context will be analyzed. The second key step is the comparison of DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ). This means that all dates in the DateKey column that are before the current pivot table row context will be calculated.
If we put this measure on a table, we’ll get the correct numbers but we will have one issue remaining.
The formula returns a number for dates that have no sales. We need to add some error handling, which is outlined below.
Cumulative Sales (Correct) :=
IF (
COUNTROWS ( FactSales ) > 0,
CALCULATE (
[Total Sales],
FILTER (
ALL ( DimDate[Datekey] ),
DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
)
),
BLANK ()
)
The IF Function checks to make sure that there are sales in the current selected context, otherwise returning blank. You can see the difference between the two measures below:
If you have any questions for me, you can reach me via LinkedIn or in the PowerBI Community.
This solution has gotten me closer. Very close. Instead of using Blank() I had used 0. However, I'm thinking I would need Month = 3 for Customer Rank = 1 to be 2. Not sure to add or somehow have that Cumulative count represent when it's blank for that Month.
Month 1 2 3 4 5 6 7 8 9 10 11 12
Customer Rank
1 1 2 3 5 7 9 10 13 17 22 23
2 1 2 3 5 7 9 10 13 17 22 23
3 1 2 3 5 7 9 10 13 17 22 23
4 1 2 3 5 7 9 10 13 17 22 23
Thank you!!
If Call Counts is a measure, then you shouldn't need the SUM, just CALCULATE([Call Counts],FILTER...)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |