Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Top 10 filter Pareto with Date Slice

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:

pareto1.PNG

 

Wrong:

pareto2.png

 

 

 

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]

 

 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@Anonymous 

 

I would say you need to use ALLSELECTED instead of ALL in your measures.

Here is an example with random data:

The model:

Model.JPG

 

And the results:ALL DATESALL DATES

 

FILTERED DATESFILTERED DATES

 

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)

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

1 REPLY 1
PaulDBrown
Community Champion
Community Champion

@Anonymous 

 

I would say you need to use ALLSELECTED instead of ALL in your measures.

Here is an example with random data:

The model:

Model.JPG

 

And the results:ALL DATESALL DATES

 

FILTERED DATESFILTERED DATES

 

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)

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.