cancel
Showing results for 
Search instead for 
Did you mean: 
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 , and Fortune 500 Companies Analysis

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 , and Fortune 500 Companies Analysis
Ito_c
Frequent Visitor

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 , and Fortune 500 Companies Analysis
Ito_c
Frequent Visitor

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 , and Fortune 500 Companies Analysis
Ito_c
Frequent Visitor

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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors