cancel
Showing results for
Did you mean:
Helper II

## Help with this...

HI Everyone....

I have this table in my PowerBI (am using direct query) the original table is much bigger, this just a sample,

And i need to create a measure to:

How many Customer by District by Date

In target district, each target for each distric

I need to sum how many customer and sum each target to make a gauge.

I got this in PwerBI

2 ACCEPTED SOLUTIONS
Microsoft

Ooops,

I cut and paste the wrong measure in

```Measure2 = IF(
HASONEVALUE(
'Table1'[Target]
),
MAX(Table1[Target]),
SUMX(
SUMMARIZE(
'Table1',Table1[Date],Table1[District],
"Max Per Day",MAX('Table1'[Target])),[Max Per Day])
)```

Proud to be a Datanaut!

Microsoft

Oh yeah, that's not ideal

Please try this slight tweak  🙂

```Measure2 = IF(
ISFILTERED(
'Table1'[Target]
),
MAX(Table1[Target]),
SUMX(
SUMMARIZE(
'Table1',Table1[Date],Table1[District],
"Max Per Day",MAX('Table1'[Target])),[Max Per Day])
)```

Proud to be a Datanaut!

14 REPLIES 14
Microsoft

I created some simple calculated measures using the following forumlas

```Count CustID = CALCULATE(DISTINCTCOUNT(Table1[CustomerID]))

Target District = CALCULATE(MAX(Table1[Target]))```

But equally you might be able to achieve it by dragging the feild to the visual and setting the aggreation to be Count (Distinct)

Proud to be a Datanaut!

Helper II

Thanks.. to take from you time

But I need is a total of TargetDistrict (5+3+4) = 12

Microsoft

Proud to be a Datanaut!

Microsoft

This is looking better.

```Measure = IF(
HASONEVALUE(
'Table1'[Target]
),
MAX(Table1[Target]),
SUMX(
SUMMARIZE(
'Table1',Table1[Date],
"Max per day",MAX('Table1'[Target])),[Max per day])
)```

Proud to be a Datanaut!

Helper II

HI,

I send you sample data of tabla1.

 DATE District CustID Target 3/26/2017 0:00 AGENCIA 10 HD 410791 2 3/26/2017 0:00 AGENCIA 10 HD 704000 2 3/26/2017 0:00 AGENCIA 11 HD 633772 4 3/26/2017 0:00 AGENCIA 11 HD 633811 4 3/26/2017 0:00 AGENCIA 11 HD 67401 4 3/26/2017 0:00 AGENCIA 11 HD 704064 4 3/26/2017 0:00 AGENCIA 11 HD 704067 4 3/26/2017 0:00 AGENCIA 11 HD 704070 4 3/26/2017 0:00 AGENCIA 11 HD 704082 4 3/26/2017 0:00 AGENCIA 11 HD 78323 4 3/26/2017 0:00 AGENCIA 12 HD 309645 4 3/26/2017 0:00 AGENCIA 12 HD 704054 4 3/26/2017 0:00 AGENCIA 12 HD 704086 4 3/26/2017 0:00 AGENCIA 12 HD 704087 4 3/26/2017 0:00 AGENCIA 12 HD 704089 4 3/26/2017 0:00 AGENCIA 12 HD 704090 4 3/26/2017 0:00 AGENCIA 12 HD 704093 4 3/26/2017 0:00 AGENCIA 12 HD 704094 4 3/27/2017 0:00 AGENCIA 11 HD 550445 4 3/27/2017 0:00 AGENCIA 12 HD 564173 4 3/28/2017 0:00 AGENCIA 11 HD 704192 4 3/28/2017 0:00 AGENCIA 12 HD 46266 4 3/28/2017 0:00 AGENCIA 12 HD 480561 4 3/28/2017 0:00 AGENCIA 12 HD 506622 4 3/28/2017 0:00 AGENCIA 12 HD 62513 4 3/28/2017 0:00 AGENCIA 12 HD 704177 4 3/28/2017 0:00 AGENCIA 12 HD 704178 4 3/29/2017 0:00 AGENCIA 11 HD 697391 4 3/29/2017 0:00 AGENCIA 11 HD 704469 4

The Output in PWBI i need is:

• Cust-ID - count of customer (29) (ok)
• Target - is the target by day for each District (don't summarize) (ok)
• SumTarget - is the total of each target. - in this case (26)

I use sumx but i can't find the correct formula

SUMTARGET = SUMX(SUMMARIZE(Table1,Table1[TARGET]),Table1[TARGET]) (result 6)

SUMTARGET = SUMX(Table1,Table1[TARGET]) (result 112)

Microsoft

I think I see what you mean.  This sligtly modifed measure returns 30, (4+4+4+4+4+4+4+2) which I think might be the number you are after????

```Measure = IF(
HASONEVALUE(
'Table1'[Target]
),
MAX(Table1[Target]),
SUMX(
SUMMARIZE(
'Table1',Table1[Date],
"Max Per Day",MAX('Table1'[Target])),[Max Per Day])

)```

Proud to be a Datanaut!

Helper II

give me 16

Microsoft

Ooops,

I cut and paste the wrong measure in

```Measure2 = IF(
HASONEVALUE(
'Table1'[Target]
),
MAX(Table1[Target]),
SUMX(
SUMMARIZE(
'Table1',Table1[Date],Table1[District],
"Max Per Day",MAX('Table1'[Target])),[Max Per Day])
)```

Proud to be a Datanaut!

Helper II

I HAVE A QUESTION?

If I add a slicer to my PowerBI, to see just one District, look what happens...

Microsoft

Oh yeah, that's not ideal

Please try this slight tweak  🙂

```Measure2 = IF(
ISFILTERED(
'Table1'[Target]
),
MAX(Table1[Target]),
SUMX(
SUMMARIZE(
'Table1',Table1[Date],Table1[District],
"Max Per Day",MAX('Table1'[Target])),[Max Per Day])
)```

Proud to be a Datanaut!

Helper II

NOW You HIT!!!

Excellent!!! THANKS!!!

Microsoft

Sorry it took so long 🙂

Proud to be a Datanaut!

Helper II

Now and going to study each part to know exactly what's doing the formula, i never imagine use max...

Thanks * 1K =many, many thanks...

Helper II

THANKS SO MUCH!!!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!