How to ignore a slicer for one measure , but apply it on others

[ravikora]

Hi all,

Required to keep measure (AvgLTD) values unchanged/constant in that row filter context, regardless of slicer selection (Period).

I was actually already tried with REMOVEFILTERS() and ALL dax functions but not worked.

Below are tables to understand everyone on my requirement.

Table 1- DIM

Id, Date, Period
1 1/1/2020 Jan
2 1/2/2020 Feb
3 1/3/2020 Mar
4 1/4/2020 Apr

Table 2 - FACT

Period_Id, Name, TotalSales, AverageSales(TotalSales/SlicerSelectionCount), AvgLTD(Totalsales/TotalPeriodCount)
1   Product1   120   60     30
2   Product2   400   200   100
3   Product3   30     15     7.5
4   Product4   200   100   50

Slicer used to filter data:

Period
Jan
Feb
Mar
Apr

1. Both Table 1 and 2 has inner join on 'Id' and 'Period_Id' 

2. In this sample 'AverageSales' are calucalted for 'Jan-Feb' period selection from slicer.

3. 'AvgLTD' calculated on 'TotalSales/TotalPeriodCount'

Can anyone please help me for solution?

Thanks,

Ravi

[mohammedadnant]

Hi @ravikora ,

Good day,

Try allexcept DAX, for clarification pls check this video

https://youtu.be/QDiyN4fUsds

Thanks & Regards,

Mohammed Adnan

www.youtube.com/taik18

[ravikora]

@mohammedadnant i will see.. thanks very much.

[wdx223_Daniel]

@ravikora  =[TotalSales]/calculate(distinctcount(dimtable[period]),all(dimtable))

[ravikora]

Thanks @wdx223_Daniel  

[amitchandak · Accepted solution]

@ravikora ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try a measure like

calculate(divide([TotalSales],[TotalPeriodCount]), allexcept(Table, Table[product]))

[ravikora]

Thanks @amitchandak 

dax worked well and i was not used it before in proper way. Finally done with solution. Thanks once again!

calculate(divide([TotalSales],[TotalPeriodCount]), allexcept(Table, Table[product]))

Cheers,

Ravi