Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
good afternoon.
I made a pareto chart in the top 10 states, but when I apply a filter to the data slice the measure of "cumulative goals" falls apart.
correct:
Wrong:
For the measures used:
Goals = CALCULATE (SUM ('All domains - main data' [Value]); FILTER ('All domains - main data'; 'All domains - main data'[Attribute] <> "Goal Completions"))
Classification = RANKX (ALL ('All domains - main data' [Region]); [Objectives] ;; DESC)
Cumulative goals = CALCULATE ([Goals]; TOPN ([Classification]; ALL ('All domains - main data' [Region]); [Goals]; DESC)
Total = CALCULATE ([Goals]; ALLEXCEPT ('All domains - main data'; 'All domains - main data'))
% Pareto = [Cumulative targets] / [Total]
Solved! Go to Solution.
@Anonymous
I would say you need to use ALLSELECTED instead of ALL in your measures.
Here is an example with random data:
The model:
And the results:
And the measures:
Ranking of Items by target
RankX Items =
VAR calc = RANKX(ALLSELECTED('Item Dim'); [Sum of Target];;DESC;Dense)
RETURN
IF(ISINSCOPE('Item Dim'[Item]); calc)
Calculating the Cumulative Target
Cumulative Target = SUMX(
TOPN([RankX Items]; ALLSELECTED('Item Dim'[Item]); [Sum of Target]);
[Sum of Target])
% Cumulative over total
% Cumulative over Total =
VAR totaltarget = CALCULATE([Sum of Target]; ALLSELECTED('Item Dim'[Item]))
RETURN
DIVIDE([Cumulative Target]; totaltarget)
Proud to be a Super User!
Paul on Linkedin.
@Anonymous
I would say you need to use ALLSELECTED instead of ALL in your measures.
Here is an example with random data:
The model:
And the results:
And the measures:
Ranking of Items by target
RankX Items =
VAR calc = RANKX(ALLSELECTED('Item Dim'); [Sum of Target];;DESC;Dense)
RETURN
IF(ISINSCOPE('Item Dim'[Item]); calc)
Calculating the Cumulative Target
Cumulative Target = SUMX(
TOPN([RankX Items]; ALLSELECTED('Item Dim'[Item]); [Sum of Target]);
[Sum of Target])
% Cumulative over total
% Cumulative over Total =
VAR totaltarget = CALCULATE([Sum of Target]; ALLSELECTED('Item Dim'[Item]))
RETURN
DIVIDE([Cumulative Target]; totaltarget)
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |