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

Percentage value mismatch between Table value and Line and Clustered Column Chart value

Hi everyone, 

 

I'm facing a mismatch between values due my calculation DAX formula. 

I have a dataset below and I need to count the subjects by fiscal month and return a percentage of this subjects by fiscal month. For the table the result is OK, but for chart the return is only 100%.  

 

The dataset:

Fiscal_YearFiscal_MonthSubject_TitleEvaluation
FYear20FMonth11Algebra IGood
FYear20FMonth11Algebra IIBad
FYear20FMonth11Algebra IIIBad
FYear20FMonth12StatisticNeutral
FYear21FMonth01Social ScienceBad
FYear21FMonth01AlgorithmsGood

 

 

 

I used both calculation DAX formulas:

 

Spoiler
FirstCalculation = CALCULATE(DISTINCTCOUNT('Table'[Subject_Title]) / CALCULATE(DISTINCTCOUNT('Table'[Subject_Title]),ALLSELECTED('Table'[Subject_Title])))
Spoiler
SecondCalculation = CALCULATE('Table'[FirstCalculation],ALL('Table'[Evaluation]))

 

The result work fine for table, but not for the chart:

 

ForumDoubt.JPG

 

Does anyone knows how to plot the same result from column "Second Calculation" in this chart? I'm sure that the problem is related to my DAX formula, but I'm trying to use the Fiscal Month as the ALL filter without success. 

1 ACCEPTED SOLUTION

@Ito_c , as per your given data I have created % Eva Measure based on Fiscal Month.

% Eva =
VAR a_ = CALCULATE(COUNT(Table1[Fiscal_Month]))
VAR b_ = CALCULATE(COUNT(Table1[Fiscal_Month]),ALLEXCEPT('Table1',Table1[Fiscal_Month]))
RETURN DIVIDE(a_,b_)
 
Then use this on Stacked Column chart so getting the correct result.
Capture.JPG
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

View solution in original post

6 REPLIES 6
Tahreem24
Super User
Super User

@Ito_c , Which chart are you using? MAke sure you are using  Stack Column Chart instead of 100% Stack Column Chart.

Capture.JPG

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Hi @Tahreem24, using a stack I have something like:

 

ForumDoubt2.JPG

 

But the disered result is the sum of column "Second Calculation" by Evaluation by Fiscal Month: 

 

ForumDoubt3.JPG

@Ito_c, Is it possible for you to share the similar data by masking confidential data so that I will try at my end.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Hi @Tahreem24 , sorry I clicked as a solved. My dataset is the same in the description (I have 4 columns by 6 rows):

 

Fiscal_YearFiscal_MonthSubject_TitleEvaluation
FYear20FMonth11Algebra IGood
FYear20FMonth11Algebra IIBad
FYear20FMonth11Algebra IIIBad
FYear20FMonth12StatisticNeutral
FYear21FMonth01Social ScienceBad
FYear21FMonth01AlgorithmsGood

 

@Ito_c , as per your given data I have created % Eva Measure based on Fiscal Month.

% Eva =
VAR a_ = CALCULATE(COUNT(Table1[Fiscal_Month]))
VAR b_ = CALCULATE(COUNT(Table1[Fiscal_Month]),ALLEXCEPT('Table1',Table1[Fiscal_Month]))
RETURN DIVIDE(a_,b_)
 
Then use this on Stacked Column chart so getting the correct result.
Capture.JPG
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Thank you @Tahreem24 , I understood the point to use fiscal month not only in the filter, but the count of variable "a" must be the month count too. 

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.