Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have report wheer the user chooses a year and Month (Example March, 2024) via a filter. It displays Product and Sales for that Month. It also has a running total for the Sales from the start of time. All of this works.
Product | Sales | Total Sales |
Radio | 100 | 2,500 |
TV | 4,200 | 9,300 |
Toaster | 50 | 500 |
I have a card that shows the complete totals. It shows:
Sales: 4,350 | Total Sales: 9,300 |
Total Sales should show: 12,300.
The
Total Sales =
VAR SDt = SELECTEDVALUE ('DateTble'[Dt])
// Create a Running total of Sales by Product
VAR Result =
CALCULATE (
SUM ('SalesTable'[Loss Incurred]),
FILTER (ALL('SalesTable'), 'SalesTable'[AcctDt] <= SDt && 'SalesTable'[ProductID] = MAX ('SalesTable'[ProductID]))
)
RETURN Result
How do I get the correct overall total?
Hi @FPP ,
What does your data model look like, please describe it with relevant screenshot information.
Best Regards,
Adamk Kong
Thanks for the reply from @amitchandak , please allow me to provide another insight:
Hi @FPP ,
You can achieve a similar effect with the help of hasonevalue function. Refer to below formula:
TotalSales =
VAR SDt =
SELECTEDVALUE ( DateTable[Dt] )
VAR Result =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= SDt
&& 'Table'[Product] = MAX ( 'Table'[Product] )
)
)
RETURN
IF (
HASONEVALUE ( 'Table'[Product] ),
Result,
SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[Date] <= SDt ), [Sales] )
)
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, I do not know why but now my data is duplicating inside a different table visual.
This table has:
Product | Dt | Sales | Total Sales |
TV | March, 2024 | 4,200 | 9,300 |
TV | April, 2024 | 9,300 | |
etc. |
@FPP , Try like, measure should use date table in filter
Total Sales =
VAR SDt = SELECTEDVALUE ('DateTble'[Dt])
// Create a Running total of Sales by Product
VAR Result =
CALCULATE (
SUM ('SalesTable'[Loss Incurred]),
FILTER (ALL('DateTble'), 'DateTble'[Dt]] <= SDt )
)
Hi, This solution also duplicates my data inside a different table visual.
This table has:
Product | Dt | Sales | Total Sales |
TV | March, 2024 | 4,200 | 9,300 |
TV | April, 2024 | 9,300 | |
etc. |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
136 | |
111 | |
98 | |
97 | |
92 |