Tableau to powerbi migration

[krishna_1811]

I want to develop a matrix with gard hierarchy(GEO->AREA->REGION=>DISTRICT) on row headers and fiscal year on column headers and a measure on values.

the measure is calculated dynamically as per the slicer selection, in slicer I have values, customer_dp, opportunity, and customer_nagp.

i have to calculate Avg $ per Deal

the calculations are as below

Avg $ per Deal : SUM([Bookings $ > 0])/[Deal Count]

deal count: COUNTD(IF ({ FIXED [Fiscal Year],[Fiscal Qtr Number],[Opportunity/Customer DP/Customer NAGP]:SUM([Bookings $])>0}) THEN ([Opportunity/Customer DP/Customer NAGP]) END)

[Opportunity/Customer DP/Customer NAGP]:

:CASE [Opportunity/Customer DP/Customer NAGP/ SO PO Number]

WHEN 'Customer DP' THEN [End Customer DP CMAT ID]

WHEN 'Customer NAGP' THEN [End Customer NAGP CMAT ID]

WHEN 'Opportunity' THEN [Opportunity Number (copy)]

END

Bookings $ > 0:

IF ZN([Opportunity/Customer DP/Customer NAGP])<>0 THEN

(IF ({ FIXED [Fiscal Year],[Fiscal Qtr Number],[Opportunity/Customer DP/Customer NAGP]:SUM([Bookings $])})>0 THEN [Bookings $] ELSE NULL END)

END

in this you check [Opportunity/Customer DP/Customer NAGP] that the value of this part is changing dynamically with slicer selection means if you select customer_dp then End Customer DP CMAT ID will be used in the calculation similarly for others

I am using a live semantic model, maybe my understanding is incorrect, could you help me how to get the above in power bi done.

[freginier · Accepted solution]

Étape 1 : Créer la table de sélection pour le slicer

SelectionType =
DATATABLE (
"SelectionName", STRING,
{
{ "Customer DP" },
{ "Customer NAGP" },
{ "Opportunity" }
}
)

Étape 2 : Créer les mesures DAX

Remplacez 'VotreTableDeFaits' par le nom réel de votre table de faits (par exemple, 'Sales', 'Bookings').

Bookings $ pour Deals Valides =
VAR _selectedType = SELECTEDVALUE(SelectionType[SelectionName])
RETURN
SWITCH(
TRUE(),
_selectedType = "Customer DP",
SUMX(
SUMMARIZE(
'VotreTableDeFaits', -- REMPLACER
'VotreTableDeFaits'[Fiscal Year],
'VotreTableDeFaits'[Fiscal Qtr Number],
'VotreTableDeFaits'[End Customer DP CMAT ID],
"@BookingsSum", CALCULATE(SUM('VotreTableDeFaits'[Bookings $]))
),
IF([@BookingsSum] > 0, [@BookingsSum], BLANK())
),
_selectedType = "Customer NAGP",
SUMX(
SUMMARIZE(
'VotreTableDeFaits', -- REMPLACER
'VotreTableDeFaits'[Fiscal Year],
'VotreTableDeFaits'[Fiscal Qtr Number],
'VotreTableDeFaits'[End Customer NAGP CMAT ID],
"@BookingsSum", CALCULATE(SUM('VotreTableDeFaits'[Bookings $]))
),
IF([@BookingsSum] > 0, [@BookingsSum], BLANK())
),
_selectedType = "Opportunity",
SUMX(
SUMMARIZE(
'VotreTableDeFaits', -- REMPLACER
'VotreTableDeFaits'[Fiscal Year],
'VotreTableDeFaits'[Fiscal Qtr Number],
'VotreTableDeFaits'[Opportunity Number],
"@BookingsSum", CALCULATE(SUM('VotreTableDeFaits'[Bookings $]))
),
IF([@BookingsSum] > 0, [@BookingsSum], BLANK())
),
BLANK()
)

Deal Count Dynamique =
VAR _selectedType = SELECTEDVALUE(SelectionType[SelectionName])
RETURN
SWITCH(
TRUE(),
_selectedType = "Customer DP",
COUNTROWS(
FILTER(
SUMMARIZE(
'VotreTableDeFaits', -- REMPLACER
'VotreTableDeFaits'[Fiscal Year],
'VotreTableDeFaits'[Fiscal Qtr Number],
'VotreTableDeFaits'[End Customer DP CMAT ID],
"@BookingsSum", CALCULATE(SUM('VotreTableDeFaits'[Bookings $]))
),
[@BookingsSum] > 0
)
),
_selectedType = "Customer NAGP",
COUNTROWS(
FILTER(
SUMMARIZE(
'VotreTableDeFaits', -- REMPLACER
'VotreTableDeFaits'[Fiscal Year],
'VotreTableDeFaits'[Fiscal Qtr Number],
'VotreTableDeFaits'[End Customer NAGP CMAT ID],
"@BookingsSum", CALCULATE(SUM('VotreTableDeFaits'[Bookings $]))
),
[@BookingsSum] > 0
)
),
_selectedType = "Opportunity",
COUNTROWS(
FILTER(
SUMMARIZE(
'VotreTableDeFaits', -- REMPLACER
'VotreTableDeFaits'[Fiscal Year],
'VotreTableDeFacts'[Fiscal Qtr Number],
'VotreTableDeFaits'[Opportunity Number],
"@BookingsSum", CALCULATE(SUM('VotreTableDeFaits'[Bookings $]))
),
[@BookingsSum] > 0
)
),
BLANK()
)

Moyenne $ par Deal =
DIVIDE(
[Bookings $ pour Deals Valides],
[Deal Count Dynamique],
0
)

Étape 3 : Configurer le visuel Matrice et le Slicer

1. Slicer : Ajoutez un visuel Slicer et glissez-y SelectionType[SelectionName].
2. Matrice : Ajoutez un visuel Matrice.
   • Lignes : Glissez GEO, AREA, REGION, DISTRICT dans cet ordre.
   • Colonnes : Glissez Fiscal Year.
   • Valeurs : Glissez votre mesure Moyenne $ par Deal.

[krishna_1811]

this logic is very near to my numbers but not getting the exact numbers in the matrix when compared to tableau, could you refine it so that i can get exact numbers

[v-achippa]

Hi @krishna_1811,

Thank you for reaching out to Microsoft Fabric Community.

Create a hierarchy in your data model which has like GEO, AREA, REGION and DISTRICT.

Create a new table for the slicer:

SelectionType =

DATATABLE (

    "SelectionName", STRING,

    {

        { "Customer DP" },

        { "Customer NAGP" },

        { "Opportunity" }

    }

)

Add this table to your report and use SelectionType[SelectionName] in a slicer visual.

Now create below measures:

Measure for Valid Bookings:

ValidBookings$ =

VAR _selectedType = SELECTEDVALUE(SelectionType[SelectionName])

RETURN

SUMX (

    FILTER (

        ADDCOLUMNS (

            SUMMARIZE (

                'FactTable',

                'FactTable'[Fiscal Year],

                'FactTable'[Fiscal Qtr Number],

                SWITCH (

                    _selectedType,

                    "Customer DP", 'FactTable'[End Customer DP CMAT ID],

                    "Customer NAGP", 'FactTable'[End Customer NAGP CMAT ID],

                    "Opportunity", 'FactTable'[Opportunity Number]

                )

            ),

            "@BookingSum", CALCULATE(SUM('FactTable'[Bookings $]))

        ),

        [@BookingSum] > 0

    ),

    [@BookingSum]

)

Measure for Deal Count:

DealCount = VAR _selectedType = SELECTEDVALUE(SelectionType[SelectionName])

RETURN

COUNTROWS (

FILTER (

ADDCOLUMNS (

SUMMARIZE (

'FactTable',

'FactTable'[Fiscal Year],

'FactTable'[Fiscal Qtr Number],

SWITCH (

_selectedType,

"Customer DP", 'FactTable'[End Customer DP CMAT ID],

"Customer NAGP", 'FactTable'[End Customer NAGP CMAT ID],

"Opportunity", 'FactTable'[Opportunity Number]

)

),

"@BookingSum", CALCULATE(SUM('FactTable'[Bookings $]))

),

[@BookingSum] > 0

)

)

Final measure for Avg per Deal:

AvgPerDeal = DIVIDE([ValidBookings$], [DealCount], 0)

In a Matrix visual, in Rows add your hierarchy like this GEO --> AREA --> REGION --> DISTRICT. In the Columns add Fiscal Year, in Values add the Avg per deal measure. And add the slicer using the SelectionType[SelectionName].

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @krishna_1811,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[bhanu_gautam]

@krishna_1811 Create a hierarchy in your data model that includes GEO, AREA, REGION, and DISTRICT.

Add a slicer to your report with the options: Customer DP, Opportunity, and Customer NAGP.

Dynamic Selection Measure:

DAX
SelectedValue =
SWITCH(
TRUE(),
SELECTEDVALUE(SlicerColumn) = "Customer DP", [End Customer DP CMAT ID],
SELECTEDVALUE(SlicerColumn) = "Customer NAGP", [End Customer NAGP CMAT ID],
SELECTEDVALUE(SlicerColumn) = "Opportunity", [Opportunity Number (copy)]
)

DAX
BookingsGreaterThanZero =
IF(
NOT(ISBLANK([SelectedValue])),
IF(
CALCULATE(SUM([Bookings $]), ALLEXCEPT(Table, Table[Fiscal Year], Table[Fiscal Qtr Number], Table[SelectedValue])) > 0,
[Bookings $],
BLANK()
),
BLANK()
)

DAX
DealCount =
CALCULATE(
DISTINCTCOUNT([SelectedValue]),
FILTER(
Table,
CALCULATE(SUM([Bookings $]), ALLEXCEPT(Table, Table[Fiscal Year], Table[Fiscal Qtr Number], Table[SelectedValue])) > 0
)
)

DAX
AvgPerDeal =
DIVIDE(
CALCULATE(SUM([Bookings $]), Table[BookingsGreaterThanZero] > 0),
[DealCount]
)

Add a matrix visual to your report.
Set the row headers to the hierarchy (GEO -> AREA -> REGION -> DISTRICT).
Set the column headers to the fiscal year.
Set the values to the AvgPerDeal measure.