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
ghamiz
Frequent Visitor

New Table Creation Summarize Average of Average Problem & Measure Filters Not Working in New Table

I am relatively newbie in world of DAX, and stuck for almost a week on a problem. Would really appreciate your help on this.

Background:

I have a telecom network data for NPS in "nps" table. Data is at Site, Cluster and Region levels.

Mapping is like:

Site -> Cluster -> Reg -> Network

 

To calculate Network NPS I defined measure:

NPSNetwork = CALCULATE( 'nps'[NPS], REMOVEFILTERS() )

 

To calculate Region NPS I defined measure:

NPSRegion = CALCULATE( 'nps'[NPS], ALLEXCEPT('nps', 'nps'[Reg]) )
 
where NPS is another calculated measure:
NPS =
var denom = COUNT( 'nps'[Customer] )
return
DIVIDE(
100*(
CALCULATE( COUNT( 'nps'[Customer] ), 'nps'[Customer] = "Promoter" ) -
CALCULATE( COUNT( 'nps'[Customer] ), 'nps'[Customer] = "Detractor" )
), denom)
 

My problem is that when in same table I generate visual on region, the Total i.e. Network value from NPSRegion measure is correct 18.15:

nps_table.PNG

 

 

 

 

 

 

 

Question 1:

But then I created a calculated table at Region level from "nps" table and used average of "NPS" measure, but the resultant Total value is not correct. It yields 17.97 instead of 18.15. That value is the average of resultant 9 values, what we commonly refer to as average of average problem. How this can be solved? I solved it using another measure in the new table referring to columns of nps table, that I don't think is an effective approach.

 

New regional level table query:

NPS_Region = SUMMARIZECOLUMNS( nps[Reg], "Reg_NPSRegion_NU", nps[NPSRegion] )
region_table.PNG
 
 
 
 
 
 
 
 
I had a work around to define another measure in the new table referring to columns of old table:
Reg_NPSRegional = CALCULATE( 'nps'[NPS], ALLEXCEPT('NPS_Region', 'NPS_Region'[Reg]) )
 
Question 2:
I similarly created another table at Cluster Level and now I want to put regional NPS to compare every cluster value to it's regional value, but the regional NPS measure does not give correct value with Cluster Filter. I tried experimenting with filters, calculate, AllExcept but to no avail. I just couldn't make it work:
cluster_with_region.PNG
 
 
Reg_NPSRegional2 =
IF(
HASONEVALUE( 'nps'[Reg] ) || HASONEVALUE( NPS_Region[Reg] ) || HASONEVALUE( Cluster_Region_Mapping[Reg] ) || HASONEVALUE( Region_Circle_Mapping[Reg] ),
'nps'[NPS],
CALCULATE( 'nps'[NPS], ALL('nps') )
)
 
Reg_NPSRegional3 =
IF(
HASONEFILTER( 'nps'[Reg] ) || HASONEFILTER( NPS_Region[Reg] ) || HASONEFILTER( Cluster_Region_Mapping[Reg] ) || HASONEFILTER( Region_Circle_Mapping[Reg] ),
'nps'[NPS],
CALCULATE( 'nps'[NPS], ALL('nps') )
)
 
 
3 REPLIES 3
Anonymous
Not applicable

I don't have time right now to deal with this but I can give you some advice. First, measures should never be preceded by the name of the table they belong to. NEVER. They must stand on their own. Second, using ALLEXCEPT without a deep understanding of what it does in different contexts is a very good and fast way to get wrong figures.
harshnathani
Community Champion
Community Champion

Hi @ghamiz ,

 

Can you share sample data and expected output.

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Hi, expected output of Question 1 is already shared. See second image Total Row, compare that to first image Total Row For Question 2, expected output is:

 

Question_2_expected_output.PNG

 

 

 

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.

Top Solution Authors