# Search:

Type: Posts; User: citizenbh; Keyword(s):

Page 1 of 5 1

1. ## Thread: DAX Formula help

by citizenbh
Replies
5
Views
854

### Re: DAX Formula help

If there are no additional variants, import Table2 (Column Mix) into the Power Query and in the Advanced Editor paste the following code:

let
Source =...
2. ## Thread: Countif in power bi

by citizenbh
Replies
14
Views
2,071

### Re: Countif in power bi

Try this:

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type text}}),
#"Duplicated Column" =...
3. ## Thread: averages from price change data

by citizenbh
Replies
1
Views
412

### Re: averages from price change data

In PowerPivot make new Column [Mont]:

=FORMAT([Date],"MMMM")

and create Pivot Table :
Rows:Month
Columns: Material Name
Value: Average for Price
4. ## Thread: Park Run Data - Scotland

by citizenbh
Replies
21
Views
911

### Re: Park Run Data - Scotland

This is Excel file with data Connection only!

https://www.dropbox.com/s/mkpcpihjci6ga0a/Park%20Run%20Data%20Scotland_All_Races.xlsx?dl=0
5. ## Thread: Park Run Data - Scotland

by citizenbh
Replies
21
Views
911

### Re: Park Run Data - Scotland

My solution has all the races from 1 to 359.
There is a table with a history of the race:

https://www.parkrun.org.uk/standrews/results/eventhistory/
6. ## Thread: Park Run Data - Scotland

by citizenbh
Replies
21
Views
911

### Re: Park Run Data - Scotland

My solution with all the races:

let
Source = Web.Page(Web.Contents("https://www.parkrun.org.uk/standrews/results/eventhistory/")),
Data0 = Source{0}[Data],
#"Changed Type" =...
7. ## Thread: form from/to areas of single lines works very slow

by citizenbh
Replies
21
Views
1,292

### Re: form from/to areas of single lines works very slow

There's a lot going on here.
I was away for the holidays.
Excellent solution with GroupKind.Local
Respect sandy666!!
8. ## Thread: form from/to areas of single lines works very slow

by citizenbh
Replies
21
Views
1,292

### Re: form from/to areas of single lines works very slow

I saw the error in the last code. There are overlapping numbers. The same County / City are in different groups of numbers!

FINAL:

let
Source =...
9. ## Thread: form from/to areas of single lines works very slow

by citizenbh
Replies
21
Views
1,292

### Re: form from/to areas of single lines works very slow

I think this is the Right Solution:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Sorted Rows" = Table.Sort(Source,{{"zip", Order.Ascending}}),
#"Grouped...

by citizenbh
Replies
3
Views
472

Cheers!
11. ## Thread: form from/to areas of single lines works very slow

by citizenbh
Replies
21
Views
1,292

### Re: form from/to areas of single lines works very slow

My Code:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Sorted Rows" = Table.Sort(Source,{{"zip", Order.Ascending}}),
12. ## Thread: Power Pivot - COUNT IF

by citizenbh
Replies
3
Views
472

### Re: Power Pivot - COUNT IF

Solution to Excrel PowerPivot:
Create tWeekNo and Calendar tables

Measures:

mWeekNo: = AVERAGE ([WeekNo]) i
mCountDoc: = CALCULATE (DISTINCTCOUNT ([DOC]), FILTER (Table1, [Status]> = 10),...

by citizenbh
Replies
3
Views
687

### Re: Formula help

You are welcome!

by citizenbh
Replies
3
Views
687

### Re: Formula help

Method 2
- Create a new column

[IC group]= IF (LEFT (Table1 [Customer Name], 3) = "I / C", "IC_Group", Table1 [Customer Name])

- From PowerPivot, create a Pivot Table with the following...

by citizenbh
Replies
3
Views
687

### Re: Formula help

If I understand, try this:

Sum Revnue I/C = CALCULATE (SUM[Revnue], LEFT(Table1[Customer Name],3 )= “I/C” )
16. ## Thread: How to calculate percentage of parent and grand total on a measure (Count) field

by citizenbh
Replies
5
Views
895

### Re: How to calculate percentage of parent and grand total on a measure (Count) field

The above solution is for Excel
on DropBox you have a Power Bi Desktop file
5 measures should be created to get the scale (column)% Parent via the IF command

...
17. ## Thread: How to calculate percentage of parent and grand total on a measure (Count) field

by citizenbh
Replies
5
Views
895

### Re: How to calculate percentage of parent and grand total on a measure (Count) field

Cange: Remove Rows: Count and add in ∑ Values: 1x Count and format ∑Count
Result Table:

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
Data1
Data2
Data3
Data4
Sum of...
18. ## Thread: How to calculate percentage of parent and grand total on a measure (Count) field

by citizenbh
Replies
5
Views
895

### Re: How to calculate percentage of parent and grand total on a measure (Count) field

Your starting data must be in this form:

<tbody>
Data1
Data2
Data3
Data4
Count
19. ## Thread: Lookup from different table in Dax Measures

by citizenbh
Replies
3
Views
547

### Re: Lookup from different table in Dax Measures

Create the third table:

State_Code, Tax Rate
MH, 20%
TM, 18%
UP, 15%

and define relationships.
20. ## Thread: Create Bar Chart With Average Line running through

by citizenbh
Replies
2
Views
520

### Re: Create Bar Chart With Average Line running through

Make new column Avg:

Avg=SUM(Table1[% Complete])/COUNT([Function])

Insert Combo Chart, with bar chart ([%Complete]) and line chart ([Avg])
21. ## Thread: HELP Transferring a basic Excel formula into a new column in Power BI

by citizenbh
Replies
3
Views
485

### Re: HELP Transferring a basic Excel formula into a new column in Power BI

Or other way:

Insert next code in Advanced Editor:

let
Source =Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed...
22. ## Thread: Need help to learn how to rearrange a table from a PowerQ Wizard

by citizenbh
Replies
1
Views
355

### Re: Need help to learn how to rearrange a table from a PowerQ Wizard

Insert Table1 in Power Query without blank row
Duplicate Table1 and rename to Table2.
In Advanced editor insert next codes for:

Table2:

let
...
23. ## Thread: in power query, add a blank row with a change in value

by citizenbh
Replies
9
Views
1,792

### Re: in power query, add a blank row with a change in value

Still a bit with totals per group:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text},...
24. ## Thread: in power query, add a blank row with a change in value

by citizenbh
Replies
9
Views
1,792

### Re: in power query, add a blank row with a change in value

And another variant:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Amount",...
25. ## Thread: PowerPivot - Searching Between Tables

by citizenbh
Replies
10
Views
540

### Re: PowerPivot - Searching Between Tables

The previous formula (VBA Geek) works for the Projects table.

Create a column in the Users Table:

=
CALCULATE (
COUNTROWS ( Projects ),
FILTER ( Projects, SEARCH ( Users[User...
Results 1 to 25 of 116
Page 1 of 5 1