Forum Discussion
First Time Poster: Disconnected Tables & Aggregating Totals based on measures. Having trouble!
Ok I have a structure similar to:
TransactionsTable:
Fields:Start Date, End Date, Type, Value
DimDateTable: (currently connected on TransactionsTable[StartDate]:DimDate[Date])
Fields: Date
DimStatusTable: (disconnected)
Fields: Active, InProgress, Expired, (and a few others)
Measures: selected Status (from slicer)
ZMeasures:
Measures: Status (this works out the status for each row in the TransactionsTable. This varies depending on dates selected in date slicer and type (and other attributes) on the TransactionsTable.
So far so good.
I can slice between the date slicer and the DimStatus slicer to show the TransactionTable with:
Date, Type, Value, Status (from Status Measure)
Status is working as expected and intended (Status changes dynamically on the visualization based on dates chosen and other attributes).
Content of the visual changes based on DimDates and DimStatuses chosen. So far so good.
What I am struggling with is I would now like to show cards with totals for each of the Statuses.
E.G: Active : Sum of all Values if the Transactions have a status of "Active" ... and so on
I’m a bit new to all of this and have spent hours trying to figure out how to solve.
If anyone has any pointers I’m listening. Not necessarily looking for a solution just direction to target some further learning would be great.
If I have not explained myself well enough let me know and I will try and expand.
Happy To do a Moch up pbix if that is required as well?
Thanks
Hi barryc ,
Based on the "status filter" you create as the filter condition, you can get the total of the Status.
Try:
Measure = CALCULATE(SUM(TransactionTable[Value]),FILTER(ALL(TransactionTable),DimStatus[Status Filter]=1))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
barryc , If you have status on transaction table then
Active value =
calculate(Sum(TransactionsTable[value]), filter(TransactionsTable, TransactionsTable[Status] ="Active"))
if not , a logic like
Active value =
calculate(Sum(TransactionsTable[value]), filter(TransactionsTable, isblank(TransactionsTable[End date]) ))
Thanks for the quick reply
Status does not exist on the TransactionsTable as it is dynamic - based on dates chosen.I also dont think the second logic will work If I could do a filter based on the dynamic status - now that would get me somewhere.
Mock Up file created:
https://1drv.ms/u/s!Am26UCUW42k4gd1lNr7zuQ2pCdEBlQ?e=gm0fF8Hopefully that helps with my descriotions above
Hi barryc ,
Based on the "status filter" you create as the filter condition, you can get the total of the Status.
Try:
Measure = CALCULATE(SUM(TransactionTable[Value]),FILTER(ALL(TransactionTable),DimStatus[Status Filter]=1))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
