Forum Discussion

Anonymous

Not applicable

1 year ago

Solved

Tableau to powerbi migration

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.

Need Help

freginier
1 year ago
É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
Slicer : Ajoutez un visuel Slicer et glissez-y SelectionType[SelectionName].
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.

5 Replies

bhanu_gautam
Super User
1 year ago
Anonymous 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.
freginier
Solution Sage
1 year ago
É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
Slicer : Ajoutez un visuel Slicer et glissez-y SelectionType[SelectionName].
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.
- Anonymous
  Not applicable
  1 year ago
  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
    Community Support
    1 year ago
    Hi Anonymous,
    
    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