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

Quick Measure-Difference from filtered value shows different totals depending on where placed

Hey guys, this is my first post here.  This forum has helped me a lot though.  I will start by saying I'm not a pro at this.  I like designing dashboards, and try to use only quick measures if I can get away with it.  Anyway, I have a dashboard that brings in multiple tables using star schema.  My dashboard uses measures to compare the two main tables (time eval and late time).  The visualization I'm focusing on here is a combo clustered column chart.  

Time Eval Timely Trend.jpg

 The image doesn't seem to want to post clearly.  Anyway it shows two colums in most months.  The first is the 2021 %timely column and the second is the 2022 % timely column.  

 

There is a secondary line that is supposed to show the percentage difference between 2022 and 2021.  

 

So here is the wierd thing.  I used a few measures to get to the % timely. 

The first is: 

Org minus Org =
COUNTA('Time Eval'[Org]) - COUNTA('Late Time'[Org])
 
I then take that measure and divide by the original count to get the % Timely:
%Timely =
DIVIDE([Org minus Org], COUNTA('Time Eval'[Org]))
 
So, the final measure is a quick measure called Difference from Filtered Value which should give me the percentage difference between 2022 and 2021:
%Timely difference from 2021 =
VAR __BASELINE_VALUE = CALCULATE([%Timely], 'Date Lookup'[Logical Date ID].[Year] IN { 2021 })
VAR __MEASURE_VALUE = [%Timely]
RETURN
    IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)
 
Here is the wierd issue.  If I put the above messure in the Tooltips it will give me the correct answer.  If I put it into the line value it gives me a lower %.  When I click on the line and view in a table it gives me extra totals (instead of staying on the 2022 difference line).  So, the table shows 2021% timely; %Timely difference from 2021 (not necessary); 2022%timely; %Timely difference from 2021(which is the correct number); then a completely different area call %Timely difference from 2021 which is a completely different(and wrong number).
  Majeebus_0-1664997799393.png

So the wierd thing is that if I put the measure into any other field (like the column field, or if I change to a line graph and place in the main line field, or the tooltips) it shows me the correct answer which is the first %timely difference from 2021.  If, however, I place that measure in the secondary line field in the combo clustured column chart it shows the second %timely difference from 2021 (which is the wrong answer).  

 

Is there any way to somehow take that second %Timely difference from 2021 out so the line shows the correct number?

1 ACCEPTED SOLUTION
Majeebus
Frequent Visitor

YES!!!!!!@!!  I FIGURED IT OUT!!!!!  

So I took the original algorithm here:

Majeebus_1-1665064744676.png

And added an additional calculation to the VAR_MEASURE_VALUE field here:

Majeebus_2-1665064784481.png

This now shows the correct information!  Thanks!

View solution in original post

4 REPLIES 4
Majeebus
Frequent Visitor

YES!!!!!!@!!  I FIGURED IT OUT!!!!!  

So I took the original algorithm here:

Majeebus_1-1665064744676.png

And added an additional calculation to the VAR_MEASURE_VALUE field here:

Majeebus_2-1665064784481.png

This now shows the correct information!  Thanks!

v-xiaotang
Community Support
Community Support

Hi @Majeebus  

Thanks for reaching out to us.

>> So the wierd thing is that if I put the measure into any other field (like the column field, or if I change to a line graph and place in the main line field, or the tooltips) it shows me the correct answer which is the first %timely difference from 2021. 

I suspect this phenomenon is context related, please refer How To Apply Context Concept in DAX — DAX in Power BI — Chapter 3 | by Arpita Ghosh | Analytics Vidh...

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

You have me pretty confused.  The article you listed shows context by adding a filter.  I already have the filter in the measure, and I would think it's an explicit filter.

Majeebus_0-1665063910241.png

Can you explain where you think the explicit context of the measure is incorrect?  The only place I would think I could add an additional explicit filter would be on the VAR_MEASURE_VALUE+[%Timely] line, but when I try that it breaks the algorithm.  

Majeebus
Frequent Visitor

Sorry just reread my original post.  I know why the table has the extra %timely difference from 2021 lines.  The first column after the 2022 column is the tooltips and the last column is from the secondary line.  Sorry for the confusion.

 

I still don't know why placing that measure in the secondary line gives me the wrong percentages.  Is this an actual coding error with power BI maybe? 

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.