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,
To begin with, I have created 3 measures to calculate Total Fees and do a month-on-month comparison as follows:
1. TTL Fees = Calculate(sum('Table1'[Amount]), 'Table1'[AccountLevel3]="Fees - Income")
2. TTL Fees LM = CALCULATE([TTL Fees],DATEADD('Table1'[Month-Year],-1,MONTH))
3. TTL Fees Inc Var = if([TTL Fees LM] = BLANK(), "N/A", [TTL Fees]-[TTL Fees LM])
I'm able to generate a table visualization as attached
However, I am not able to use a slicer based on my calendar table for this table visualization as it'll result in blanks for the 'TTL Fees LM' measure since it uses 'Table1'[Month-Year]. Kindly advise on how to resolve this.
Tks: -Nik
Solved! Go to Solution.
Hi @Anonymous !
You are most likely missing the relation between the Date column of your Table1 and the CalendarTable (in my case DateDIM). And make sure you use the CalendarTable value in your slicer.
Thanks for your swift reply, Roland.
I have actually gotten the results as you have gotten too. However, when it comes to slicing to a specific period, I only want to table visual to only display info for the selected period i.e. year(s) in this case.
In my new visual below, I have selected '2017' but the table visual also/still shows info for '2018' which I don't wish to have:
I need the table visual to only show info for '2017'.
Thanks: -Nik
Hi @Anonymous ,
The issue should be related your measures in the table visual. We can use blank() instead of "N/A". If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Thanks for your message too, Frank.
Yes, after opting to use blank() instead of "N/A", I have managed to solve the problem & now able to do use a year-to-select slicer too.
Kind regards, -Nik
Hi @Anonymous !
You are most likely missing the relation between the Date column of your Table1 and the CalendarTable (in my case DateDIM). And make sure you use the CalendarTable value in your slicer.
Thanks for your swift reply, Roland.
I have actually gotten the results as you have gotten too. However, when it comes to slicing to a specific period, I only want to table visual to only display info for the selected period i.e. year(s) in this case.
In my new visual below, I have selected '2017' but the table visual also/still shows info for '2018' which I don't wish to have:
I need the table visual to only show info for '2017'.
Thanks: -Nik
Hi @Anonymous ,
The issue should be related your measures in the table visual. We can use blank() instead of "N/A". If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Thanks for your message too, Frank.
Yes, after opting to use blank() instead of "N/A", I have managed to solve the problem & now able to do use a year-to-select slicer too.
Kind regards, -Nik
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |