Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi ,
I have three tables.
1. DATA Table
Period Amount Budget
201901 200 A1
202001 3000 B1
201901 250 C1
2. Period Table:
Period Year Month
201901 2019 Jan
202001 2020 Jan
3. Budget Table
Budget Budget_Name
A1 Actual
B1 Budget
C1 Forecast
Datatable is linked to Period and Budget table.
Could you please help me in calculating a single measure that sums of Amount in data table based on below criteria:
(Year="2019" from period table and Budget="A1" from budget table) OR ( Year="2020" from period table and Budget="B1" from budget table)
Thanks in advance for your support.
Hi kpandey,
Please try the following measure:
Hi SaraMissBI,
Thanks for the DAX measure. This is measure is now working. However, as you mentioned below about more dynamic measure. I would like to hear about this. As I will have multiple conditions like below and summing up each will consume more time. Could you help me in preparing measures with multiple conditions? For example.
The table and data structure will be the same. Conditions are:
(Year="2019" from period table and Budget="A1" from budget table) OR ( Year="2020" from period table and Budget="B1" from budget table) OR (Year="2018" from period table and Budget="A1" from budget table) OR ( Year="2020" from period table and Budget="A1" from budget table)
Thanks in advance for your support.
Hi @Anonymous ,
I am glad my answer helped you. For me to help with making your measure more dynamic, I need to know what will control the conditions? is it for example a menu/slicer? If you are going to get the total for all possible cases then you won't need a measure just the amount will do the job.
Thank you,
Hi @saraMissBI ,
Thanks again for your response.
I am looking for the option without using slicers for the possible conditions. However, as you mentioned below on the way to get total for all the possible cases without using measure. I would love to hear on this.
Also, on my data table the amount filed is YTD. Period filed is in the "yyyymm" format with data type text. Are there any ways to get the monthly figures from the YTD figures for my data?
Thanks in advance for your support.
Thank you very much.
This measure is working.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |