Forum Discussion
SUM rows filtered by data segmentation
Hello everyone,
I'm trying to SUM a column called "Offline Time". This column has 300 rows. But I don't want to SUM all these rows, I want to SUM using data segmentation as a parameter.
For example, I select 1 week, in data segmentation, this means that I need to SUM only seven rows in "Offline Time" and not all 300 rows.
Thanks in advance.
11 Replies
Hi:
If you have a column for week no. in your fact table and you have built your slicer off of that, your measure should work dynamically.
The measure would be something like Total = SUM(TableName[Offline Time)
It will give you the correct answer if your column next to Offline Time has week no.
Here is calculated column for week no:
Week No = WEEKNUM(TableName[Date Field])
If this doesn't work can you post some more info about your question? (The model, what the table has for columns, relationships,..)
Thanks
Thanks for the answer 😉
But I believe that the key is to sum the column with some condition.
I'm trying something like that:SUM Offline time = CALCULATE(SUMX(Offline, Offline[Offline time(minutes)]), ALLSELECTED(Offline[Alert Date]))
I'm not sure if I should use ALLSELECTED. What I intend to do is to sum the Offline Time(minutes) using the offline as a parameter. Notice that in data segmentation I'm using Calendar Offline[Date] instead of Offline[Alert Date]Hi:
If I can see the data (some data with exepcted result) I can try to answer more thoruoghly.
The way yo have your data model and the visual you are attempting to create pretty much drives the DAX solution.
Thanks!
Hi:
In your date table, please mark column date as Date Table. Then add calc columns to date table.
WEEK No. = WEEKNUM(DateTableName[Date])
YEAR = YEAR(DateTableName[Date])
Month No. = MONTH(DateTableName[Date])
Connect your date table date filed to your facttable date column, to create a relationship.
Stay with original measure Total = SUM(Table[offlinetime])
Add slicer taking week no field from date table. picture below.
Use slicer to get the the values you want for sum of offlinetime.
Hi:
That doesn't make too much sense to me as I can't see your model. Can you supply this file for a more complete answer?
Thanks
