Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm sure this is a really simple issue that I am completely missing but when I am calculating a column total based on two variables;
Sector "WCB"
RSP Period "20/09"
Solved! Go to Solution.
Hi @btwilkins ,
Since I don’t know the formula of the measure 'XX PTL ODP ', I can’t find the cause of the error, you can try to do so.
XXPTL TOTAL =
IF(
HASONEVALUE('Fct SG PTL ODP Period'[Sector]),
'Fct SG PTL ODP Period'[xx PTL ODP],
SUMX( ALL('Fct SG PTL ODP Period'), 'Fct SG PTL ODP Period'[xx PTL ODP])
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
My issue isn't being resolved because XX PTL ODP is a calculated Measure IF's and SUMs don't work?
Hi @btwilkins ,
"My issue isn't being resolved because XX PTL ODP is a calculated Measure IF's and SUMs don't work?"
It is indeed for this reason, please give a sample data and give the formula og the measure 'XX PTL ODP'.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@btwilkins - Let's back up. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Apologies,
See my Fct table structure below;
[Table 1]
Sector | RSP Period | n other filter columns | XX PTL ODP (Calculated Measure) |
A | 1 | filter values | 0.020202 |
A | 1 | filter values | 0.013423 |
A | 1 | filter values | 0.006644 |
A | 2 | filter values | 0.000135 |
A | 2 | filter values | 0.006914 |
A | 3 | filter values | 0.013693 |
A | 3 | filter values | 0.020472 |
A | 4 | filter values | 0.027251 |
B | 1 | filter values | 0.034030 |
B | 2 | filter values | 0.040809 |
B | 3 | filter values | 0.047588 |
B | 4 | filter values | 0.054367 |
B | 1 | filter values | 0.061146 |
B | 2 | filter values | 0.067925 |
A | 4 | filter values | 0.074704 |
A | 4 | filter values | 0.081483 |
A | 4 | filter values | 0.088262 |
A | 4 | filter values | 0.095041 |
A | 4 | filter values | 0.101820 |
The calculation I want to do as a calculated measure (NOT column) is;
- £xxxx (fixed currency value) is shared out by Sector AND Period as a proportion of the SUM of all the XX PTL ODP in that Sector for that period, so I need to determine what is the proportion of XX PTL ODP for each row, what sector & period they are in then multiply the proportion of the fixed currency value by that proportion (for EVERY row).
Therefore;
CALCULATED MEASURE =
£xxx fixed currency value (for that sector & period) x ((XX PTL ODP)/ SUM (XX PTL ODP(for that sector&period)))
For each row in the dataset
I'm expecting a £ figure, I have set £xxx fixed currency values for each Sector Period
There are only two sectors but periods will be continuously increasing so I will update this code with every upload of new data as the £xxx are only known subsequently.
Hi @btwilkins ,
Has your problem been solved?
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @btwilkins ,
Since I don’t know the formula of the measure 'XX PTL ODP ', I can’t find the cause of the error, you can try to do so.
XXPTL TOTAL =
IF(
HASONEVALUE('Fct SG PTL ODP Period'[Sector]),
'Fct SG PTL ODP Period'[xx PTL ODP],
SUMX( ALL('Fct SG PTL ODP Period'), 'Fct SG PTL ODP Period'[xx PTL ODP])
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |