Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
unnijoy
Post Partisan
Post Partisan

Formula Help

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
1 ACCEPTED SOLUTION

@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

12 REPLIES 12
Zubair_Muhammad
Community Champion
Community Champion

HI @unnijoy

 

Try this MEASURE

 

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

Regards
Zubair

Please try my custom visuals

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.

Hi @unnijoy

 

I get correct results with the revised table even

 

Please see attached file

 

Fhelp.png


Regards
Zubair

Please try my custom visuals

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

@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

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.

Hi @unnijoy

 

Could you share your file?


Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad

 

Am not getting a opetion to upload the file.

@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

Hi @Zubair_Muhammad,

 

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.