Forum Discussion

sixtoquiles's avatar
sixtoquiles
Helper II
9 years ago
Solved

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 Custome...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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's avatar
    Phil_Seamark
    9 years ago

    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's avatar
sixtoquiles
Helper II
9 years ago

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)

 

 

 

 

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
    sixtoquiles
    Helper II
    9 years ago

    give me 16

     

     

     

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      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's avatar
        sixtoquiles
        Helper II
        9 years ago

        THANKS SO MUCH!!!