cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

I want to see the average percent of two columns

Hello!

 

I've tried asked for help before but still havent solved it. I bet its really simple, but i'm new on this and its hard.

 

I got these two columns:

 

MSP/HSP 1tim = IF('logg'[Time]>1/24/60*60;"Missed";"Completed")

 

and

 

LSP 2tim = IF('Logg'[Time]>1/24/60*120;"Missed";"Completed")

 

 

I want to merge these two columns together and see the average percent of how many "Missed" and "Completed" there is. I cant find a way to do this. Can someone please help me?

10 REPLIES 10
Anonymous
Not applicable

What do you mean by "merge"? If these 2 cols are in the same table (and they are), then how can they be "merged"?

By the way, 1/24/60*60 = 1/24, and 1/24/60*120 = 1/12.

From this one can conclude, that if [LSP 2tim] = "Missed", then the other one is "Missed" as well. But the "Completed" entries for the [LSP 2tim] can be split into "Missed" and "Completed".

So, what's the rule for a merger?

Best
D
Greg_Deckler
Super User
Super User

Sould be sooooo much easier with sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

But, perhaps something like:

Measure Percent Missed =
  VAR __Table = 
    UNION(
      SELECTCOLUMNS('Table',"Value",[MSP/HSP 1tim]),
      SELECTCOLUMNS('Table',"Value",[LSP 2tim])
    )
  VAR __Count = COUNTROWS(__Table)
  VAR __Missed = COUNTROWS(FILTER(__Table,[Value] = "Missed"))
RETURN
  __Missed / __Count

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

The Table looks kinda like this. If MSP/HSP Inställelsetid 1tim its <1hour it says "Avklarat" (Completed) and if its >1hour it says "Missat" (Missed). And the same goes with "LSP inställelsetid 2tim". 

I want to see the average percent of how much is "Avklarat" (completed) and "Missat (missed) from both "MSP/HSP inställelsetid" 1tim and "LSP inställelsetid 2 tim" in a new column.

 

average percent.PNG

Anonymous
Not applicable

Can someone try to help me? I really need help with this

Anonymous
Not applicable

Sorry, your requirements are not really understandable. Can you please craft a little table and show exactly what you mean by:

the average percent of how much is "Avklarat" (completed) and "Missat (missed) from both "MSP/HSP inställelsetid" 1tim and "LSP inställelsetid 2 tim" in a new column.

I can't understand what all this means. Please make a small table with the calculations and the result.

Thanks.

Best
D
Anonymous
Not applicable

I dont know how to make a table and explain better there sorry.. I will try my best explaining

 

Will this help maybe?

help average percent.PNG

Anonymous
Not applicable

Hi. First of all, you have to have measures for what you want to do. You can't achieve what you want by just dropping fields into your visuals. Secondly, you should create a good, star-schema model to be able to do things correctly. I attach a file that shows you how to do what you want and it also highlights the most important bit: how to model data. Fact tables should only contain facts and keys to dimensions. All their columns must be hidden. Only measures are allowed to be visible. Slicing is done through dimensions only.

 

If you decide to by-pass the rules, you'll be suffering a lot. GUARANTEED.

 

There are 2 different 'averaging' measures in the model. For the data in there right now they give the same answer but that's a coincidence. If you include more dimensions and start slicing by them, the figures returned by them will differ and hence the pie charts will differ as well. So, please be careful with this. Just don't assume - test.

 

Best

D

Anonymous
Not applicable

So I shouldnt use those formulas that I have? Because it might not be correct after some time? I should only use measures ?

Anonymous
Not applicable

You should ALWAYS use DAX measures for aggregation. Fact tables should never, ever be exposed. Forget about any ad-hoc measures created by PowerBI automatically. It only leads to errors and frustration.

By the way, have you checked the file I attached to my latest post?

Best
D
Anonymous
Not applicable

Im sorry but I have no idea how to give you sample data, its the data I wrote from the beginning, those 2 columns.. I've tried so much in several days now to get this fixed. Starting to give up... I cant simple give out the pbix file here, its too much sensitive info

 

I got those two columns that are based on this:

Time = IF('Logg'[hour]<24&&'Loggt'[Minute]<60; [Hour] & ":" & [Minute] & ":" & [Seconds];"23:59:59")

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.