I have created two measures that show correct counts:
No. of available entries = 1834
No. of expected entries = 1846
I need to get a percentage of No. of available entries / No. of expected entries. Here is the formula I am using:
Percent = DIVIDE([No. of available entries],[No. of expected entries])
This results in %100.00 rather than %99.35. Can’t figure out why? any thoughts?
Thank you
Hi @Helal,
With the limited information you’ve provided, this will turn into a guessing game… But I’ll play one round. Do both your measures [No. of available entries] and [No. of expected entries] show the expected result on a Card visual?
If not you’ll need to provide it the required context to calculate these values.
@Helal ,
I’ve replicated your measures exactly, and the result comes out correct:
The only thing I can think is whether you have the formatting set correctly for the measure?
Thank you Brian,
It’s odd that despite correct formatting, I don’t get what you get. I slightly changed the formula and added the BLANK that you have to no avail.
Just to be clear, so there are no filters applied on any of the depicted Cards? And you aren’t rounding to 2 decimals? 
Have you tried inserting a new card and applying the measure to that? Or do a quick rebuilt in a new PBIX file?
@Helal ,
If you can please post your PBIX work in progress, I’m confident @Melissa or I can figure out quickly what’s going on here.
Thanks.
Hi Brian,
Yes, I did insert a new card and set decimals to 2. But re-sharign PBIX and if there are any filters applied, the data I am working is secure and can’t be shared. I am trying to see how I can strip the identifiable data and then share the PBIX. The data structure is rather complex. I have joined 12+ SQL Tables to create one data set. Then, I kept IDs from each of the Tables and unipivot the rest of the columns (about 500 columns now unipivtoed int one column). So, here how I calculated Number of expected entries. THe “Batch1_Variable_List” is the unipivoted column. Grad_Subject_Cnt is coming from the anchor Table (Grad_List) where the other 12+ Tables were Left Joined to this table.
No. of expected entries = CALCULATE([Grad_Subject_Cnt],REMOVEFILTERS(QC_Report_Batch1[Batch1_Variable_List]))
No. of availabe entries varies for each Table. For example, I have a Table named “Auditc”. Where Auditc.Subjectid is kept out of the unipiviot. I calculated No. Of enteries for Auditc as:
No. of available entries Audit = DISTINCTCOUNTNOBLANK(QC_Report_Batch1[AUDITC_Subject]).
Filters applied: Out of 12 Tables, 3 are excluded by first filter (Blanks as well). Also, since the main Table (QC_Report_Batch1) is also joined with another Table (Data Dictionary) and there is a column in data dictionary that distinguishes between variable/column types (Numeric, Categorical, Datatime, and Text), the second filter excludes Datatime & Text and keeps only Numerical and categorical variables. in Sum, data are restructured and two filters were applied. But I though, if no. of expected and available enteries are calculated correctly, which they were, then it’s a matter of Diving the two!
Thank you,
Helal
Well that should solve it then, increase the number of decimals.
Hi Melissa,
Brian asked if I inserted a new card to try and insert the % into the card with decimals be set at 2. My response was yes, I did both but it produced same results (100%). That was the reason I went on length to describe the data structure and talked about the two filtered that are applied. You posted a response that insertign a new card and settign the % variable to 2 decimals should work. Unfortunately, it diodn’t.
Helal
@Helal this was a control question, rounding to 2 decimals when dealing with percentages results into no decimals for a percentage… however if you’re not rounding this should not be a problem
@Helal ,
I’m afraid without a PBIX file, I’m at a loss as to how to proceed in resolving this problem. Here’s a post I put together on masking confidential data that may be helpful in you creating a file replicating the problem you are having that you can share.
It also might be helpful to get a screenshot of your current data model, since in my experience many “DAX” problems are actually data modeling problems.
Thanks.
Hi Brian,
Maybe I should start by outlining the goal and purpose of creating this report and discuss the data structure I have created. Then, as you indicated, the problem could be the data model more than anything else. I am also sharing a sample pbix file. Here is detailed info on the project:
Goal: To fully automate and streamline QC Reports for each of the 95 Tables (Forms). The final report(s) will be refreshed with new data every quarter
Data Source: 95 SQL Tables. All Tables have different columns and different numbers of records. One common element among all Tables is Subject(ID).
QC Reports Templates: In a nutshell, although data varies by Tables (Forms), QC reports follow the same Template for all. This means, that there are certain visualizations for categorical variables such as Basic Matrix and perhaps Bar Charts. By the same token, for numerical variables, I have to present Box Plot. Of course, both Templates include various Filters, page navigations, etc.
Current Data Modeling: Since it will be too time-consuming, to work on each of the 95 Tables and create visualization and report one by one, I first divided the 100 Tables into Batches of 15 to 20 Tables (Forms) each. Then, in SQL Server, I LEFT joined the first 20 Tables with the Subject Master list and dumped the results into one single Table: dbo.QC_Report_Batch1. Then, I connected Power BI Desktop to the dbo.QC_Report_Batch1. I ended up having 500+ columns (variables). To streamline and follow the Templates (described above), I kept Subject (IDs) from each Table including Subject from the master Table, and Unipivot the rest of the columns into two columns (Batch1_Variable_List and Batch1_Data). So, Batch1_Variable_List includes all the variables, minus Subject, in all 20 Tables and Batch1_Data includes their corresponding values. Then, I joined this Table (QC_Report_Batch1) with an Excel File (Data Dictionary). The data dictionary includes all information on all columns (variables) in the 100 Tables. The info such as variable names, variable labels, value labels, and data type (Categorical, Numerical, DateTime, Text). So, by joining the two tables, I have now a single column (Data Type) where I can easily create the same Template say categorical Template for All variables that fall under categorical and do the same for numerical.
Sample PBIX file (QC First Batch Sample): The sample file I am sharing includes only two Tables (Forms). Please CTLR + Click on the Menu Button on the Top left to see Filters.
My Questions:
Thank you,
Helal
QC First Batch Sample.pbix (1.7 MB)
Hi @Helal,
When I drag both your measures into a table, I get this result:
I can’t refresh the model but do you get the same results, when you refresh the data?
Hi Melissa,
That was my mistake not to update the data. The good news is I figured that out. However, give the project goal, do you think the structure of the data file is suitable for what I would like to acheive?
Thank you,
Helal
Hi Melissa again,
A couple more questions:
Thank you,
Helal
Hi @Helal,
I have no records at all with [Batch1_Data]=“9999” in the provided sample from July 15th
Toggle this one off, mayby that is what you are looking for:
Thank you, Melissa and Brian. I appreciate both of your help.
Helal
Hi @Helal, did the response provided by Brian and Melissa help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
