Dividing by total of Distinct Count

[ajohn1]

Very very new to Power Bi, so please take it easy on me.

So if you are a excel wizard you probably arlready noticed that my Total for 'History CIs' is not adding up correctly, however that is not the problem. What you should see is Equipment Failure = 102.5, Lightning = 130.5, and Other = 298.5.

Here is my function History CIs = (CALCULATE(
SUM('Diagnostic_Data_Poutage'[CI]),
'Diagnostic_Data_Poutage'[Yr_Type] IN { "History" }))/ CALCULATE(DISTINCTCOUNT(Diagnostic_Data_Poutage[Year]),Diagnostic_Data_Poutage[Yr_Type] in {"History"})

I know that it is only dividing the History CIs column by 1 because that particular cause only shows up in one year. However, I need this data to divide by 2 (the Total of the 'Measure' column) because the column contains 2016 & 2017 data. This error doesn't show at all when no filters are selected because every cause has happend in every year. I been working on this for a week now, can someon please help.

[v-juanli-msft]

Hi @ajohn1

I believe it is possible for your requirement, could you show some example data?

Best Regards

maggie

[ajohn1]

Are you asking that I upload a sample of the excel document or the .pbix?

[Ashish_Mathur]

Yes.  Both.

[Ashish_Mathur]

Hi,

Explain the business question.  Show your data and the expected result.

[Seward12533]

Hi, in power BI each cell is calcualted indepently based on the filter conext of that cell (or point/bar on graph) so each of your rows are calculated in the Summary Clause but the TOTAL has no filter context so is including all values independent of Summary Clause context.  Its not the SUM of the the rows above it. 

One solution will be turn off the Total or to block the measure from displaying on summary rows where it makes no sense.  In your case you can use ISFILTERED to test to see if its filtered by year and only calcualte if that is true.  Note I didn't include an ELSE statement. This is one of the key differences between how DAX works and formulas in Excel.  If no ELSE if defined then it return NULL and nothing is displayed. You could also define an ELSE to peform a calcuation for the Total Row that works based by dividing on distinct count.  

History CIs = IF(ISFILTERED(Diagnostic_Data_Poutage[Year]),CALCULATE(
SUM('Diagnostic_Data_Poutage'[CI]),
'Diagnostic_Data_Poutage'[Yr_Type] IN { "History" }))/ CALCULATE(DISTINCTCOUNT(Diagnostic_Data_Poutage[Year]),Diagnostic_Data_Poutage[Yr_Type] in {"History"})

If you need to check to see if the slicer is being used you can use ISFILTERED(slicertable[slicercolumn]) if you  allow more than one slicer selection or HASONEVALUE(slicertable[slicercolumn]) to test and apply the else conditional formula to get the values you want to show on the Total Row. 

The solution may be more straightforward than that. 

You probably want to convert your YEARS to DATES (can use a calcualted column and the DATE function specifyin 1 fro the Month and Day) can then link that to a date table. You can then use  Date Table that is linked to  the YEAR in date format.  Then put the YEAR from the date table in ROWS section of your visual and then the TimeIntelligence of DAX will take over and your measures will may work as desired and it won't try to total years either. Though you can avoid this by putting year in the ROWS section vs Values. 

I would invest some time in understanding how filter context works in PowerBI. This video has a nice summary and I highly recommend PowerPivotPro videos, blogs and books this how I learned DAX and PowerPivot/PowerBI.