Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sotoc
Advocate I
Advocate I

Presence/Absence Counts and Percentages in a single Matrix

I have a tough time getting my head around how to show counts and %, cumulative % using a simple matrix, as my users aren't familiar with PBI yet and like to look at static "slides". 


A lot of my data is counts and % with some filters of absent/present of each attribute, like this one:

 

I have measures to count the number of items for each attribute and separate measures to calculate their % of total but can't get them into a single matrix based on present/absent values for each.  I have the format tool selection "Show on rows" = ON.

 

e.g.:

Company Ct = Count (Insight_TestOutput[Company])

PostalCode Ct = COUNT (Insight_TestOutput[Postal Code])
PostalCode % = [PostalCode Ct]/[Company Ct]


"Company" is my base value. It is always 100% as it is a required input. So I use a Company Ct measure as my denominator. I need to complete the chart past the Present-Count column.

 

Also, any websites with beginner/intermediate matrix lessons are much appreciated!

 

   
AttributePresent CountPresent %Absent CountAbsent %
Company100100%00
Address9090%1010%
City2525%7575%
State5555%4545%
Postal1515%8585%
Country9898%22%
Telephone2525%7575%
     

 

 

9 REPLIES 9
v-jiascu-msft
Employee
Employee

Hi @sotoc,

 

Can you share your file? A dummy is enough. We can put them in a Matrix. Did you get any error messages? Please also refer to power-bi/desktop-matrix-visual.

 

Best Regards,

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale @v-jiascu-msft, will you please give me an idea of turnaround on this? I've never submitted a request from the help team so don't have a reference point of timing. Thank you!


Carly

Hi Carly,

 

Sorry, I can't access the link. Seems I don't have permission.

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Please try this one. Thank you!

 

 Sample Data - Absence/Presence

Hi there Dale, were you able to open the second file? Thanks so much, 


Carly

Hi Carly,

 

I'm afraid we have to change the format of the original data table. Please refer to the demo in the attachment.

Presence_Absence_Counts_and_Percentages_in_a_single_Matrix

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for this demo @v-jiascu-msft!

 

I can't figure out what I am missing here, but when I reproduced your unpivot and created your measures, my Absent counts/% are good. But I get the file's total row count for each of the attributes. My source .csv file is clean without spaces in the cells. 

 

If I enter a new measure "Count Diff" as the difference between the Present Count (showing 49 for each attribute), my result will be the true "Present" values you show. 

 

Count Diff = CALCULATE(COUNT(CustomerInput[Value]) - ([Absent]))

 

AttributeCount of ValuePresentPresent %AbsentAbsent %Count Diff
Account4949100%0049
Address4949100%714%42
City4949100%714%42
Country4949100%24%47
Postal4949100%612%43
State4949100%714%42
Total294294100%49100%265

 

Thanks again for any additional guidance on producing the correct Present results.

 

Carly

Hi Carly,

 

Can you send me the file? It would be easy to troubleshoot with file.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale, thank you for the assistance. I am not getting errors but want to find out the most efficient way to create this matrix.

 

Sample Data File - C. Soto

 

 Much appreciated!

Carly

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.