Forum Discussion
Disable aggregate
Hi All,
I have two tabs, first tab is full of slicers and second tab works based on slicer selection in first tab.
I have some slicers in tab 1, mainly fiscal period and periodic (MTD,QTD,YTD).
I have a Matrix visual in second tab. If i select particular month from Month slicer (Ex : 2017006) and YTD in periodic slicer, i just want to show 2017001----2017006 in matrix visual.
In Matrix visual i'm showing CY Sales and LY Sales. For the above selection, currently matrix visual aggregate 2017001 to 2017006 data. How to show individual month data based on slicer selection.
Thanks,
Thiyaga
Baskar v-jiascu-msft v-yuta-msft Anonymous
5 Replies
Hi Thiyagu,
There could be a trick of independent slicer table. Please check out the demo in the attachment.
1. Create a new table as a slicer. Don't establish any relationship.
2. Create a measure like this.
Measure = CALCULATE ( SUM ( 'Fact'[Value] ), FILTER ( 'Calendar', 'Calendar'[Date] <= MAX ( 'Slicer'[Date] ) && 'Calendar'[Date] >= FIRSTDATE ( DATEADD ( Slicer[Date], -5, MONTH ) ) ) )Best Regards,
Dale
Hi Thiyagu,
Can you share your pbix file please? Please mask the private parts first.
Generally, if you select 2017006, only the data of 2017006 will be showed up. Or how can we call it slicer? But we can make some changes in the model. Maybe a measure also could be helpful.
Best Regards,
Dale
I agree with you. But users requirement like this. I cant share pbix file, but i can help you with more snip/examples if you want.
My measure is like this,
_Measure =
Var _curMonth= IF(Hasonefilter(fiscalperiod),values(fiscalperiod),Max(fiscalperiod))
Var _curQtr = IF(Hasonefilter(fiscalquarter),values(fiscalquarter),Max(fiscalquarter))
Var _curYear = IF(Hasonefilter(fiscalyear),values(fiscalyear),Max(fiscalyear))
Var _Period = IF(Hasonefilter(periodic),values(periodic),"MTD")
Var _Scale = IF(Hasonefilter(scale),values(scale),"Whole Number")
Var _Value = SWITCH (
TRUE (),
_Period = "QTD", CALCULATE (
SUM ( actuals),
FILTER (
ALL (
table[Fiscal Period],
table[Fiscal Quarter],
table[Fiscal Year]
),
table[Fiscal Quarter] = _curQtr
&& table[Fiscal Year] = _curYear
&& table[Fiscal Period] <= _curMonth && table[category] = "Sales"
)
),
_Period = "YTD", CALCULATE (
SUM ( table[actuals] ),
FILTER (
ALL (
table[Fiscal Period],
table[Fiscal Quarter],
table[Fiscal Year],table[category]
),
table[Fiscal Year] = _curYear
&& table[Fiscal Period] <= _curMonth && table[category] = "Sales"
)
),
_Period = "MTD", CALCULATE (
SUM ( table[actuals] ),
FILTER (
table,
table[Fiscal Period] = _curMonth && table[category] = "Sales"
)
)
)
Return
SWITCH (
TRUE (),
_Scale = "Whole Number", _Value,
_Scale = "Thousands", DIVIDE ( _Value, 1000 ),
_Scale = "Millions", DIVIDE ( _Value, 1000000 )
)Thanks,
Thiyaga
