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.
I Have a table that consists of transactions as follows:
txn date type ref budget actual variance
01/10/2016 inv 1 25.00 30.00 5.00
02/10/2016 inv 2 20.00 18.00 -2.00
03/10/2016 inv 1 22.00 22.00 0.00
04/10/2016 inv 1 27.00 31.00 4.00
05/10/2016 inv 2 20.00 19.00 -1.00
The 'variance' is a measure I have created to subtract budget from actual.
I wish to show a summary of this in a table as follows:
ref budget actual variance overruns
1 74.00 83.00 9.00 2
2 40.00 37.00 -3.00 0
I can achieve most of this however I don't appear to be able to count the overruns.
I thought it would be something like CALCULATE((COUNTROWS('MYTABLE'),FILTER('MYTABLE',[OVERRUNS]>0)) but this gives the wrong results and appears to count all records.
Any help would be much appreciated.
Solved! Go to Solution.
In this scenario, if you want to calculate the overuns for each ref, you should filter the table based on Variance in your COUNTROWS calculation. If you want to count the ref which overuns above zero, you need to distinct count ref column.
Overruns = IF(CALCULATE(COUNTROWS(),FILTER(Table8,[Total Variance]>0))=BLANK(),0,CALCULATE(COUNTROWS(),FILTER(Table8,[Total Variance]>0)))
Count Ref above zero = CALCULATE(DISTINCT(Table8[ref]),FILTER(Table8,[Overruns]>0))
Regards,
In this scenario, if you want to calculate the overuns for each ref, you should filter the table based on Variance in your COUNTROWS calculation. If you want to count the ref which overuns above zero, you need to distinct count ref column.
Overruns = IF(CALCULATE(COUNTROWS(),FILTER(Table8,[Total Variance]>0))=BLANK(),0,CALCULATE(COUNTROWS(),FILTER(Table8,[Total Variance]>0)))
Count Ref above zero = CALCULATE(DISTINCT(Table8[ref]),FILTER(Table8,[Overruns]>0))
Regards,
Thanks for the feedback
Managed to sort the problem with your advice
Cheers
Hi,
Hope I understood your query correctly,
Let's begin from the end 🙂
I accomplished it with the following steps:
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 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |