Help with this...

[sixtoquiles]

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

[Phil_Seamark · Accepted solution]

Ooops,

I cut and paste the wrong measure in

Please try this

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

[Phil_Seamark]

Hi @sixtoquiles

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)

[sixtoquiles]

Thanks.. to take from you time 

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

[Phil_Seamark]

Hi @sixtoquiles

 Please see my post below

[Phil_Seamark]

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

[sixtoquiles]

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)

[Phil_Seamark]

Hi @sixtoquiles

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

[sixtoquiles]

give me 16

[Phil_Seamark · Accepted solution]

Ooops,

I cut and paste the wrong measure in

Please try this

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

[sixtoquiles]

I HAVE A QUESTION?

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

[Phil_Seamark · Accepted solution]

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

[sixtoquiles]

NOW You HIT!!!

Excellent!!! THANKS!!! 

[Phil_Seamark]

Sorry it took so long 🙂

[sixtoquiles]

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...

[sixtoquiles]

THANKS SO MUCH!!!