Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]))
Hi @ravikora ,
Good day,
Try allexcept DAX, for clarification pls check this video
Thanks & Regards,
Mohammed Adnan
@ravikora =[TotalSales]/calculate(distinctcount(dimtable[period]),all(dimtable))
@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]))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |