cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sixtoquiles
Helper II
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.

 

Capture.JPGCapture2.JPG

I got this in PwerBI

02-04-2017-07-59-29.png

 

2 ACCEPTED SOLUTIONS

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

14 REPLIES 14
Phil_Seamark
Microsoft
Microsoft

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)

 

count.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks.. to take from you time 

 

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

 

Hi @sixtoquiles

 Please see my post below


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

HI,

 

I send you sample data of tabla1. 

 

DATEDistrictCustIDTarget
3/26/2017 0:00AGENCIA 10 HD4107912
3/26/2017 0:00AGENCIA 10 HD7040002
3/26/2017 0:00AGENCIA 11 HD6337724
3/26/2017 0:00AGENCIA 11 HD6338114
3/26/2017 0:00AGENCIA 11 HD674014
3/26/2017 0:00AGENCIA 11 HD7040644
3/26/2017 0:00AGENCIA 11 HD7040674
3/26/2017 0:00AGENCIA 11 HD7040704
3/26/2017 0:00AGENCIA 11 HD7040824
3/26/2017 0:00AGENCIA 11 HD783234
3/26/2017 0:00AGENCIA 12 HD3096454
3/26/2017 0:00AGENCIA 12 HD7040544
3/26/2017 0:00AGENCIA 12 HD7040864
3/26/2017 0:00AGENCIA 12 HD7040874
3/26/2017 0:00AGENCIA 12 HD7040894
3/26/2017 0:00AGENCIA 12 HD7040904
3/26/2017 0:00AGENCIA 12 HD7040934
3/26/2017 0:00AGENCIA 12 HD7040944
3/27/2017 0:00AGENCIA 11 HD5504454
3/27/2017 0:00AGENCIA 12 HD5641734
3/28/2017 0:00AGENCIA 11 HD7041924
3/28/2017 0:00AGENCIA 12 HD462664
3/28/2017 0:00AGENCIA 12 HD4805614
3/28/2017 0:00AGENCIA 12 HD5066224
3/28/2017 0:00AGENCIA 12 HD625134
3/28/2017 0:00AGENCIA 12 HD7041774
3/28/2017 0:00AGENCIA 12 HD7041784
3/29/2017 0:00AGENCIA 11 HD6973914
3/29/2017 0:00AGENCIA 11 HD7044694

 

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)

 

Capture.JPG

 

 

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

give me 16give me 16

 

 

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

I HAVE A QUESTION?

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

 

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

NOW You HIT!!!

Excellent!!! THANKS!!! Man Happy

Sorry it took so long 🙂

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

THANKS SO MUCH!!!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.