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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Matrix Drill Down error

Hi team ,

I have a matrix with rows,columns and values as the following :

adithyanv_0-1698687694020.png

 

   In Values field :  i have averages (measure)

 

This is the following matrix :

adithyanv_1-1698687694251.png

 

The problem starts when i try to drill down my matrix from area level view to cluster level view :

 

adithyanv_2-1698687740740.png

 

 

the row wise averages are correct but the column wise averages are wrong

also when i use the "DRILL DOWN" functionality by selecting/Right -clicking the area row inorder to view the cluster level view :

the averages at column level are wrong

adithyanv_3-1698687740741.png

 

 

how can we tackle this problem?

 

Thanks in Advance

 

AVERAGEX(ADDCOLUMNS(SUMMARIZE('SP: Heatmap - Sub','SP: Heatmap - Sub'[Area],'SP: Heatmap - Sub'[Control]),"Value",CALCULATE(AVERAGE('SP: Heatmap - Sub'[Values]))),[Value])

3 REPLIES 3
speedramps
Super User
Super User

Try this

 

 

Answer = 
SWITCH(TRUE(),
HASONEVALUE(Stats[Cluster]) && HASONEVALUE(Stats[Column]),SUM(Stats[Amount]),
AVERAGEX(Stats,Stats[Amount])
)

 

 

Click here to download solution 

I am not sure I understand your problem.
We want to help you but your description is too vaugue. Please write it again clearly.

Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want to do. 

Please just give a simple non technical functional description of what you want, then let us suggest the DAX. Thank you.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
Also provide the example desired output, with a clear description of the process flow.

Remember not to share private data ... we don't want you to get into trouble. 😧

Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.

You will get a quick response if you put time, care and effort into writing clear problem descriptions.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming

Anonymous
Not applicable

Hi @speedramps ,

Thanks for the Reply,

To be more specific , 

my goal is to find averages at area and cluster level.

as you are aware when we include 2 values under rows in a matrix it enables us to have drill through.

Area and cluser are my drill through option

when i drill from area level to cluster level , the vertical averages are showing wrong or same as area level averages

 

Thanks

 

Please click on the link to downlaod the solution.

It does exactly what you asked.

I asked you for more example data but you did not go to the effort to provide any.

Whereas I have taken a lot of uppaid time and effort to answer your question with an example and link.
Please be polite and accept this free solution and click thumbs up, if you wish to to receive more free support.
If you want somethings else but didnt ask the right question, then close this question and create a new one.

 

Click here to download solution 

 

Answer = 
SWITCH(TRUE(),
HASONEVALUE(Stats[Cluster]) && HASONEVALUE(Stats[Column]),SUM(Stats[Amount]),
AVERAGEX(Stats,Stats[Amount])
)

 

 

speedramps_0-1698744920540.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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