Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have three tables, Projections, Actuals, and % Variation. The Variation is calculated using the measure
Volume (Variation) = CALCULATE([Volume (Actuals)])/CALCULATE([Volume (Projection)])-1
Volume (Actuals) and Volume (Projection) are calculated as follows from the main data table. The volume column is just a simple value of 1 for each record to enable counts.
Volume (Actuals) = CALCULATE(SUM('MASTER TABLE'[Volume]),'MASTER TABLE'[Type]="Actuals")
Solved! Go to Solution.
Hi @Will_Rayner ,
Note that in a Power BI visual, the total of a measure is not just the sum of all rows. it is a calculated value affected by measure.
If you want to calculate the sum of measure value, please use sumx() function.
https://docs.microsoft.com/en-us/dax/sumx-function-dax
total = sumx(filter(allselected('table'),expression),[measure])
And if you want to show [measure] on row level and show total on total level, please use ISINSCOPE() or ISFILTERED() function.
https://docs.microsoft.com/en-us/dax/isinscope-function-dax
Please refer the formula.
total =
var _total = sumx(filter(allselected('table'),expression),[measure])
return
IF(ISINSCOPE([row field]),[measure],_total)
Best Regards,
Jay
Hi @Will_Rayner ,
Note that in a Power BI visual, the total of a measure is not just the sum of all rows. it is a calculated value affected by measure.
If you want to calculate the sum of measure value, please use sumx() function.
https://docs.microsoft.com/en-us/dax/sumx-function-dax
total = sumx(filter(allselected('table'),expression),[measure])
And if you want to show [measure] on row level and show total on total level, please use ISINSCOPE() or ISFILTERED() function.
https://docs.microsoft.com/en-us/dax/isinscope-function-dax
Please refer the formula.
total =
var _total = sumx(filter(allselected('table'),expression),[measure])
return
IF(ISINSCOPE([row field]),[measure],_total)
Best Regards,
Jay
Hi:
You can use SUMX to fix:
=SUMX(
Total Measure =
var vtable = SUMMARIZE('MASTER TABLE',
'MASTER TABLE'[Description],
"Variance", Volume (Variation))
return
IF(HASONEVALUE( 'MASTER TABLE'[Description]),[Volume Variation], SUMX(vtable,[Variance]))
Volume Variation(New) =
var vtable = SUMMARIZE('MASTER TABLE',
'MASTER TABLE'[Description],
"Variance", Volume (Variation))
return
IF(HASONEVALUE( 'MASTER TABLE'[Description]),[Volume Variation], SUMX(vtable,[Variance]))
Hi,
Is this solution intended to calculate the variance between the Projection count and the Actual Count? I can't see how those inputs come into this formula.
I was hoping for a DAX solution that, from the measures I already have in place, I could copy and paste as a new measure to use?
Thank you for the response.
I have used the suggested alternative calculation for the Volume Variation, however this still returns the incorrect value in the total line. i.e. it is still showing 8% for February rather than the expected result of 12%.
Hi:
I created your data and can paste link to file here. I hope this helps. You'll notice one solution is with one fact table and the same solution with two fact tables. (which you described)
https://drive.google.com/file/d/1jGo9p026vlCCsCcXkmmbqp0YaSo0cUhv/view?usp=sharing
Hi,
Many thanks for your solution here, and from your image, it looks like this is doing as I require, however unfortunately despite a few attempts, I am unable to open any of your solution documents in a way that they make any sense to me. I just see filles of strange symbols!
Are you able to post a solution as as response rather than links to files? This may then assist other users with a similar problem
Many thanks
Hi:
I guess seeing a sample file will help. Usually the SUMX fix works on totals, so I'm not sure why this did not work in this case.
Can you change your volume % chg measure to:
Volume (Variation) = DIVIDE([Volume (Actuals)], [Volume (Projection)])-1
and see if that helps, then use the new code?
A simplified MASTER TABLE would be:
Type | Description | Start Date | Volume |
Actual | Slips | 01/02/2022 | 1 |
Actual | Slips | 01/02/2022 | 1 |
Actual | Slips | 01/02/2022 | 1 |
Actual | Slips | 01/02/2022 | 1 |
Actual | Slips | 01/02/2022 | 1 |
Actual | Slips | 01/02/2022 | 1 |
Actual | Slips | 01/02/2022 | 1 |
Actual | Slips | 01/02/2022 | 1 |
Actual | Slips | 01/02/2022 | 1 |
Actual | Declarations (All) | 01/02/2022 | 1 |
Actual | Declarations (All) | 01/02/2022 | 1 |
Actual | Declarations (All) | 01/02/2022 | 1 |
Actual | Declarations (All) | 01/02/2022 | 1 |
Actual | Declarations (All) | 01/02/2022 | 1 |
Actual | Declarations (All) | 01/02/2022 | 1 |
Projection | Slips | 01/02/2022 | 6 |
Projection | Declarations (All) | 01/02/2022 | 5 |
On which my measures (and your new DIVIDE one will work.
The expected results from this should be +20% for Declarations (All), +50% for Slips, and +36% as a Total
The visualisations are simple tables
Should your SUMX be a measure? This could be where I am going wrong?
Hi Will:
I'm not sure why the shared google file won't open. I'll try something else. I wanted you to see the data model. The results for your inital question are:
I download from here.
I can see it fine. Is this any better?
@Will_Rayner ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
The data source is very simple and could easily be replicated. I'm not quite sure how to do that effectively here as the tables are large and do contain sensitive data, as does the pbxi file of which this is a tiny element.
I think it is the logic of PBI that is the issue, i.e. treating the variation measure differently in the table results, which are correct (the (1%), 34%, (2%), 197% & 5% for February), but it calculates the TOTAL differently i.e 1,371.88 against a projection of 1,225.45 should be 12% and not 8% as shown.
@Will_Rayner , seems fine to me. Try these with a small difference
Volume (Actuals) = CALCULATE(SUM('MASTER TABLE'[Volume]),filter('MASTER TABLE','MASTER TABLE'[Type]="Actuals")
Volume (Projection) = CALCULATE(SUM('MASTER TABLE'[Volume]),filter('MASTER TABLE''MASTER TABLE'[Type]="Projection")
Volume (Variation) = divide([Volume (Actuals)],[Volume (Projection)])-1
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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 |
---|---|
54 | |
27 | |
23 | |
14 | |
11 |
User | Count |
---|---|
77 | |
63 | |
44 | |
17 | |
12 |