Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Learn more

Reply
unnijoy
Post Prodigy
Post Prodigy

Formula Help

‎03-28-2018 10:59 PM
MonthCountrySiteProgram NameHC
Jan-18USGreenvilleRunning464
Jan-18USGreenvilleRunning464
Jan-18USGreenvilleRunning464
Jan-18USGreenvilleRunning464
Jan-18USGreenvilleRunning464
Jan-18USGreenvilleRunning464
Jan-18USDalevilleRunning696
Jan-18USDalevilleRunning696
Jan-18USDalevilleRunning696
Feb-18USGreenvilleRunning424
Feb-18USGreenvilleRunning424
Feb-18USGreenvilleRunning424
Feb-18USDalevilleRunning424
Feb-18USDalevilleRunning424
Feb-18USDalevilleRunning424
Feb-18USGreenvilleRunning424

 

Based on the above data i need a formula for ma power bi report in which in the table for for Jan i need the HC for US. As we can see that the program name is repeating. So i need to calcculate the HC in such a way that the formula should calulate the HC once even if the site and program name is repeating. The result should be as follows.

 

MonthcountryHC
Jan-18US1160
Feb-18US824
Labels:
Message 1 of 13
1,871 Views
0
1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion
In response to unnijoy

‎03-29-2018 04:41 AM

@unnijoy

 

Please try this MEASURE

 

MeasureF =
VAR temp =
    CALCULATETABLE ( DISTINCT ( Table1[HC] ), ALL ( Table1[Mode] ) )
RETURN
    SUMX ( temp, [HC] )

Regards
Zubair

Please try my custom visuals

View solution in original post

Message 12 of 13
1,892 Views
0
12 REPLIES 12
Zubair_Muhammad
Community Champion
Community Champion

‎03-28-2018 11:06 PM

HI @unnijoy

 

Try this MEASURE

 

Measure =
SUMX ( VALUES ( Table1[HC] ), CALCULATE ( VALUES ( Table1[HC] ) ) )

Regards
Zubair

Please try my custom visuals
Message 2 of 13
1,803 Views
unnijoy
Post Prodigy
Post Prodigy
In response to Zubair_Muhammad

‎03-28-2018 11:27 PM

Hi Thanks for the Prompt reply. A small change in the Table.

 

MonthCountrySiteProgram NameModeHC
Jan-18USGreenvilleRunningOnline464
Jan-18USGreenvilleRunningILT464
Jan-18USGreenvilleRunningOnline464
Jan-18USGreenvilleRunningILT464
Jan-18USGreenvilleRunningOnline464
Jan-18USGreenvilleRunningILT464
Jan-18USDalevilleRunningOnline696
Jan-18USDalevilleRunningILT696
Jan-18USDalevilleRunningOnline696
Feb-18USGreenvilleRunningILT424
Feb-18USGreenvilleRunningOnline424
Feb-18USGreenvilleRunningILT424
Feb-18USDalevilleRunningOnline400
Feb-18USDalevilleRunningILT400
Feb-18USDalevilleRunningOnline400
Feb-18USGreenvilleRunningILT424

 

In the Table we can see Mode. So the HC should be calculated irrespective of Mode.

By the formula that u gave the system will calculate the HC seperately for each mode. our Formula should not be based on mode.

Message 3 of 13
1,799 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to unnijoy

‎03-28-2018 11:45 PM

Hi @unnijoy

 

I get correct results with the revised table even

 

Please see attached file

 

Fhelp.png


Regards
Zubair

Please try my custom visuals
Formula Help.pbix
Message 4 of 13
1,792 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to Zubair_Muhammad

‎03-29-2018 12:12 AM

Hi @Zubair_Muhammad

 

In that output table we have to include Mode also. We have once for field come in this table. For that we got the formula. Can you please help me to get the data in the below format.

 

MonthcountryModeHC
Jan-18USILT1160
Jan-18USOnline1160
Feb-18USILT824
Feb-18USOnline824
Message 5 of 13
1,788 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to unnijoy

‎03-29-2018 12:36 AM

@unnijoy

 

Even if i include the Mode Field in the Table.. I am getting the required output

 

mode.png


Regards
Zubair

Please try my custom visuals
Formula Help.pbix
Message 6 of 13
1,782 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to Zubair_Muhammad

‎03-29-2018 12:45 AM

Hi @Zubair_Muhammad

 

I am getting it seperately. Like for December HC filters based on Online and ILT. In the top I have 4 slicers. Month, country, Program name,Mode. Is the mismatch because of this.

Message 7 of 13
1,777 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to unnijoy

‎03-29-2018 12:58 AM

Hi @unnijoy

 

Could you share your file?


Regards
Zubair

Please try my custom visuals
Message 8 of 13
1,771 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to Zubair_Muhammad

‎03-29-2018 01:14 AM

Hi @Zubair_Muhammad

 

Am not getting a opetion to upload the file.

Message 9 of 13
1,763 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to unnijoy

‎03-29-2018 04:41 AM

@unnijoy

 

Please try this MEASURE

 

MeasureF =
VAR temp =
    CALCULATETABLE ( DISTINCT ( Table1[HC] ), ALL ( Table1[Mode] ) )
RETURN
    SUMX ( temp, [HC] )

Regards
Zubair

Please try my custom visuals
Message 12 of 13
1,893 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to Zubair_Muhammad

‎03-29-2018 04:56 AM

Hi @Zubair_Muhammad,

 

Amazing. It is working. Thanks al lot for your help and time.

Message 13 of 13
1,418 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to unnijoy

‎03-29-2018 01:51 AM
MonthRegionCountrySiteProgram NameDelivery TypeHC
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaCanadaWHT-ILT205
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaUSMorrisville-ILT4
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSGreenville-ILT464
Jan-18North AmericaUSST. LOUISG2ILT20
Jan-18North AmericaUSGreenvilleC45ILT464
Jan-18North AmericaUSWHT-ILT2426
Feb-18North AmericaUSWHTG2ILT2053
Feb-18North AmericaUSWHTG2ILT2053
Feb-18North AmericaUSWHT-ILT2053
Jan-18North AmericaUSGreenville-ILT464
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSST. LOUISG2ILT20
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSGreenville-ILT464
Message 11 of 13
1,412 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to unnijoy

‎03-29-2018 01:25 AM
MonthRegionCountrySiteProgram NameDelivery TypeHC
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaCanadaWHT-ILT205
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaUSMorrisville-ILT4
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSGreenville-ILT464
Jan-18North AmericaUSST. LOUISG2ILT20
Jan-18North AmericaUSGreenvilleC45ILT464
Jan-18North AmericaUSWHT-ILT2426
Feb-18North AmericaUSWHTG2ILT2053
Feb-18North AmericaUSWHTG2ILT2053
Feb-18North AmericaUSWHT-ILT2053
Jan-18North AmericaUSGreenville-ILT464
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSST. LOUISG2ILT20
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSGreenville-ILT464
Message 10 of 13
1,731 Views
0

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All