cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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
Highlighted
Super User IV
Super User IV

Re: I want to see the average percent of two columns

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

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: I want to see the average percent of two columns

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


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Helper III
Helper III

Re: I want to see the average percent of two columns

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")

 

Highlighted
Helper III
Helper III

Re: I want to see the average percent of two columns

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

Highlighted
Helper III
Helper III

Re: I want to see the average percent of two columns

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

Highlighted
Super User IV
Super User IV

Re: I want to see the average percent of two columns

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


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Helper III
Helper III

Re: I want to see the average percent of two columns

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

Highlighted
Super User IV
Super User IV

Re: I want to see the average percent of two columns

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



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Helper III
Helper III

Re: I want to see the average percent of two columns

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors